<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Sander Mulders on Medium]]></title>
        <description><![CDATA[Stories by Sander Mulders on Medium]]></description>
        <link>https://medium.com/@sandermulders?source=rss-5ae49cc29e69------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*ru1mSO3TT1-NvOC6.</url>
            <title>Stories by Sander Mulders on Medium</title>
            <link>https://medium.com/@sandermulders?source=rss-5ae49cc29e69------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 15:05:08 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sandermulders/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Performing geospatial queries at scale]]></title>
            <link>https://medium.com/@sandermulders/performing-geospatial-queries-at-scale-7b64795d7704?source=rss-5ae49cc29e69------2</link>
            <guid isPermaLink="false">https://medium.com/p/7b64795d7704</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[apache-solr]]></category>
            <category><![CDATA[elasticsearch]]></category>
            <category><![CDATA[postgis]]></category>
            <dc:creator><![CDATA[Sander Mulders]]></dc:creator>
            <pubDate>Wed, 30 May 2018 06:31:48 GMT</pubDate>
            <atom:updated>2018-05-30T06:31:48.013Z</atom:updated>
            <content:encoded><![CDATA[<h4>On querying millions of points and billions of polygons</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TAIO1WyPPQwRR97DHso9Pw.png" /></figure><p>As you might have guessed after reading some of <a href="https://medium.com/geophy-hq">GeoPhy’s blog posts</a>: we deal with a very large amount of geospatial data. In the case of GeoPhy, we are referring to two distinct types of geospatial data:</p><ul><li><strong>Point based</strong>, having a location on the planet (latitude/longitude) and one or more attributes we typically want to aggregate. A typical example would be points representing bars (Lat/Lng + Attribute::Bar)</li><li><strong>Polygon based</strong>, like the points but with multiple locations describing a closed boundary (technically speaking we also have MultiPolygons like a boundary with holes). A typical example would a county or metropolitan area.</li></ul><p>Since data integration and maintaining a flexible, well-described data structure is one of the key activities of GeoPhy, we made the choice to use RDF as our main means of storage. There are several great extensions on the standard query language for RDF (Sparql) for performing the queries we need to do like: <a href="https://gist.github.com/p1d1d1/9bcea6d5ce29a70f0e7d2baff1ec8ad9">GeoSparql</a> and STSparql, so we felt confident going in this direction.</p><p>After some initials tests we determined that it would indeed be a great fit for us with one caveat: performing geospatial queries at this scale is really slow. Nevertheless, we felt like RDF would be a good fit and started looking for other ways to satisfy our hunger for geospatial queries.</p><h3>Technical requirements</h3><p>To give you some sense of the scale of the dataset some high level numbers of the dataset at moment of testing:</p><ul><li>350,000 <strong>Polygons</strong> describing geographical regions</li><li>10,000.000 <strong>Points</strong> describing amenities</li><li>100,000,000 <strong>Points</strong> describing buildings</li><li>1,200,000,000 <strong>Polygons</strong> describing catchments (see below), 12 per building</li></ul><p>We consider this a pretty large dataset, with a focus on the US. But in the near future, we will start expanding resulting in an increase of amenities and building. As you can see, we will have 12 extra polygons for each building with an expected target next year of 250,000,000 buildings… So scale needs to be taken into account!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rr65o_GR9X1mU-npYSTC3Q.png" /><figcaption>A sample of catchments for 10,000 buildings in the US</figcaption></figure><h4>When to query and how much?</h4><p>Our Data Management Platform [DMP] takes data as something that is always in motion: coming at different moments and in different sizes. Therefore the DMP is built as an event-driven streaming platform in its core: all our services for matching and enriching data operate automatically upon the data flowing into the platform. The DMP itself has 2 distinct phases: ingestion and enrichment. The geospatial challenge we are describing in this post is confined to the enrichment phase alone. In this phase, one of the core activities is to take a building location and enrich it with numerous datapoints describing its surroundings, i.e. the number of restaurants within a 5mins walking distance. The common way of doing this is using the buffer technique: take the lat/long of a location, draw a circle around it with a certain radius, and count all points in the circle. Early on, we have concluded in our research that using the buffer does not result in a fair representation of the actual world: there might be a river next to the building that is blocking access.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eiCoBDeppGxIVxY8Fh-0pA.jpeg" /><figcaption>A 15 and 30 minutes buffer at avg. driving speed in Manhattan NY</figcaption></figure><p>To solve this, we developed the GeoPhy Reach catchment polygon: taking all data known about the road network, we construct the polygon describing the actual accessible area. This is why every building has 12 extra catchment polygons: driving, walking and public transport catchments for various travel times. Since a circle is an easy-to-describe geospatial feature, the impact of the query is marginal compared to a geospatial query involving a polygon.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BYenS17Kj7Y-xCCTjfhigg.jpeg" /><figcaption>A 15 and 30 minutes driving distance following the road network in Manhattan NY — Produced by Geophy Reach</figcaption></figure><p>Often, the query filters on distance from the circumcenter instead of intersecting two polygons, making this much more efficient (a circle can be described in a single mathematical function where a polygon requires the actual points in the correct order). Nevertheless we favour the catchment over the buffers, since its impact on the final result can be significant.</p><p>Within our architecture, we have an Aggregation service responsible for retrieving all aggregations we would require. Since these aggregations serve as an import input for our models, there are quite a lot, more than 300 per building. There are 3 types of aggregations that we perform:</p><ul><li>Count the number of points-in-polygons given various filter criteria;</li><li>Sum/average the attributes of point-in-polygons;</li><li>Sum the area of intersecting polygons.</li></ul><p>These queries are performed on every new building inserted in our database or on every change in the aggregated data (i.e. a new restaurant is added). Our typical ingestion size is between 10.000–100.000 buildings per day with peaks hitting 1 million. To keep up with the influx of new data, we are aiming to enrich 5,000 buildings/hour, resulting in 1.5 million queries per hour or 420/second as a target value.</p><p>The shear size of data and its constant changing nature makes that there is very little to gain from caching: keeping it up to date would be equal to the amount of queries for doing it “live”.</p><p>One advantage is that the DMP is considered an internal platform with no direct access for our clients. Its main purpose is to feed our models and datastores we have for our products.</p><blockquote><strong>This makes queries per second more important than latency as long as we can have concurrency.</strong></blockquote><p>A small but important distinction, also considering the service-oriented nature of our architecture, where we can scale the number of running aggregation services.</p><h4>Summing up the requirements</h4><p>To sum up the requirements, the final geospatial storage engine must be:</p><ul><li>Able to support indexing and querying geospatial shapes, in particular points, polygons and multipolygons at scale;</li><li>Able to filter based on the geospatial features’ attribute;</li><li>Able to perform aggregation methods like SUM(), COUNT() and AVG();</li><li>Able to sum up the area of intersecting polygons;</li><li>Able to handle a constant inflow of data (important for considering the indexing mechanism).</li></ul><h3>Setup</h3><h4>The Engines</h4><p>For testing purposes we had to define which engines to use — there are many to choose from. To limit the scope, we decided on using engines that were at least known to use to reduce the amount of complexity: PostgreSQL, ElasticSearch and SOLR. SOLR might be an odd choice, but it turned out to be a pretty good engine for doing the buffer-style queries in a previous project. PostgreSQL being an obvious contender having excellent geospatial support using PostGIS. ElasticSearch was recommended by several people as being an interesting contender because of its proven way of scaling and integration of geospatial search queries.</p><ul><li>PostgreSQL 10 + Postgis 2.4</li><li>SOLR 7.2.0</li><li>ElasticSearch 6.2, ( Index settings: 1 shard, 0 replica)</li></ul><h4>The Dataset</h4><p>Since running the benchmark on the full dataset would result in creating several terabytes of data, we decided to come up with a smaller representative sample that would illustrate the issues we had in the past. The following 4 scenarios were defined:</p><ul><li>test-a1: Amenities 1<br>Real world spread throughout the United States having 1 million amenities</li><li>test-a2: Amenities 2<br>Local stress test clustered in the county of Los Angeles having 1 million densely packed points simulating amenities</li><li>test-b1: Landuse 1<br>700,000 Landuse polygons throughout the United States</li><li>test-c1: Census 1<br>33,120 Census tracts throughout the United States</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TAIO1WyPPQwRR97DHso9Pw.png" /><figcaption>1 million amenities in the US</figcaption></figure><p>The target data consists of a dataset with 10,000 random properties in the United States, enriched with 4 polygons that represent a buffer and 4 polygons that represent a catchment. The buffer radius is determined by the average catchment size to represent a similar type of aggregation. The catchment sizes are a combination of two modes of transport (walking and driving) and a 10 and 30 minute commute time.</p><h4>The test environment</h4><p>For each of the databases we set up an c5.2xlarge instance on AWS (8 vCPU, 16GB memory) and an additional instance for coordinating the tests and running jMeter. Each of the 10,000 buildings would be used in the test, having either 4 buffers or 4 polygons to query resulting in 40,000 samples. The buffer tests were done 1 and 100 threads so see how it would handle concurrency and the catchment test just at 100.</p><h3>The Results</h3><h4>Buffer Queries</h4><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8625fe5ac115e46de5a384f18a0a09df/href">https://medium.com/media/8625fe5ac115e46de5a384f18a0a09df/href</a></iframe><p>Not really much to tell from a single thread query. It does not even come close to the required 420 queries/sec</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8e6393b9723ee4451116de1be3c605ad/href">https://medium.com/media/8e6393b9723ee4451116de1be3c605ad/href</a></iframe><p>This does start to show some differences. Clearly the simple buffer style queries work really well in ElasticSearch and SOLR. ElasticSearch really shines doing point-in-buffer queries and SOLR outperforms all when it comes to buffer-polygon intersections and fast enough in the point-in-polygon. Postgresql holding the middleground being fast enough on all query styles</p><h3>Catchment Queries</h3><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a777ca779b323869680b914bbcaadb52/href">https://medium.com/media/a777ca779b323869680b914bbcaadb52/href</a></iframe><p>As expected the more complex queries are the true torture test for all databases. SOLR could not handle the large volume of points doing point-in-polygon queries. PostgreSQL remains a good performer, only having minor struggles doing the sparse (A1) queries.</p><p>As you can see ElasticSearch is missing in this result set. At the moment of testing, and even a couple of days after, ElasticSearch was still doing importing and indexing data… At low precision (&gt; 100 meters) it all went fine but lowering it to the required &lt; 10 meter put the engine to its knees. The high precision would be fine for high level aggregations but since we are typically dealing with highly localised differentiators this disqualified ElasticSearch as an option.</p><h3>Observations</h3><h4>PostgreSQL</h4><ul><li>Very very fast at indexing (and a small index disk size);</li><li>Weak performance for point in simple polygon (buffer), but still not that bad;</li><li>Similar performance for polygon in buffer. Best performance for anything to do with multipolygons (catchments).</li></ul><h4>ElasticSearch</h4><ul><li>Extremely slow at indexing;</li><li>Consumes large amount of disk space with high precision (&lt;10meter) polygons;</li><li>Best performance for point in simple polygon (buffer);</li><li>Worst for polygon in polygon.</li></ul><h4>SOLR</h4><ul><li>Very fast for buffer-polygon intersections;</li><li>Extremely slow for point-in-polygon queries;</li><li>Indexing also fairly slow.</li></ul><h3>Conclusions</h3><p>SOLR and Elasticsearch are really good at handling tons of concurrent requests for simple spatial queries. But it becomes problematic when they need to index complex polygons because it takes a very long time and the index size balloons out of proportion. This is solved by increasing the precision to 100m. Even with the lower precision Postgresql still outperforms them for multipolygon spatial queries, while also having better results. There were some cases where SOLR was wrong in the aggregation due to the 100 meter precision round off. Postgresql was chosen as the engine to move forward on.</p><h3>One more thing…</h3><p>One of the possible challenges we brought up during the process is the scaling of Postgresql to handle the 1 billion+ datasets we described in the technical requirements. There are known solutions for setting up larger PostgreSQL clusters with several read-replicas to spread the load whilst diving also into sharding the data (sharding by geographical region is a relatively simple solution here) but they are known to be tricky to setup and maintain. In the last week of the benchmark we decided on doing a “quick” test on the, then newly, released Amazon Aurora PostgreSQL cluster since this claimed to be a perfect solution to our problems</p><blockquote>“Aurora is up to five times faster than standard MySQL databases and three times faster than standard PostgreSQL databases.”</blockquote><blockquote>“Aurora features a distributed, fault-tolerant, self-healing storage system that auto-scales up to 64TB per database instance.”</blockquote><p><a href="https://aws.amazon.com/rds/aurora/">https://aws.amazon.com/rds/aurora/</a></p><p>We decided on doing a large scale test feeding it with over 1.2 billion polygons (12 polygons for 100,000,000 locations scattered throughout the US) and run the same tests. <strong>The results were almost identical to the test we did on a single instance but with 120x the amount of data.</strong> This result was astounding. Yes we compared a full cluster to a single instance… but this is a fair comparison in our minds since it took as the same amount to setup and will not cost more to maintain. Plus the promise of seamless scaling is a very nice bonus.</p><p>Many thanks to my colleagues <a href="https://medium.com/u/d8cf88e418de">Mathijs Nelemans</a>, <a href="https://medium.com/u/404b305f2f96">Rawand Hawiz (rhawiz)</a>, and Thaer for making this possible!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7b64795d7704" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Startup Journey Part 1, just making it work]]></title>
            <link>https://medium.com/geophy-hq/a-startup-journey-part-1-just-making-it-work-69ba96a8ae1a?source=rss-5ae49cc29e69------2</link>
            <guid isPermaLink="false">https://medium.com/p/69ba96a8ae1a</guid>
            <category><![CDATA[startup]]></category>
            <category><![CDATA[dashboard]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[laravel]]></category>
            <category><![CDATA[monolithic]]></category>
            <dc:creator><![CDATA[Sander Mulders]]></dc:creator>
            <pubDate>Wed, 17 May 2017 08:06:57 GMT</pubDate>
            <atom:updated>2017-05-18T16:38:11.177Z</atom:updated>
            <content:encoded><![CDATA[<p><em>This is part of a longer series focused on the technical components of where we started and where we are going.</em></p><p>Four years ago, we decided to tackle the challenge of how to find the best office space in the Netherlands. At the time, a digital solution for commercial space did not exist. We wanted to take the manual work out of finding and comparing space options for commercial tenants.</p><p>We thought it would be fairly simple, given the tangibility of real estate, the size of the industry and the (seemingly) large presence of information in the market. We quickly learned that despite the vast amounts of information available, a majority of industry decision makers continued to rely on gut feelings, opinions, personal relationships, and excel spreadsheets. We’ve come across so many excel spreadsheet set-ups driving ridiculously large portfolios, it deserves a special call out.</p><p><strong>We had a different approach, we believed in letting the data speak.</strong> At only 40,000 buildings, our first dataset was relatively small and easy to manage. We came up with a way to take data from the building itself and combine it with 50 or so peripheral characteristics to rank buildings against one another using an objective algorithm: the OfficeRank.</p><p>As you would expect, the initial modelling was done in simple spreadsheets (keeping it consistent with the industry), but we knew a “proper” database could take the results to another level. To do this, we kept it basic, sticking with a MySql, PHP, JS, HTML stack. This allowed us to quickly build out a prototype for the Dutch office market.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Z3oMsHp82MU7Y2U301bWPg.jpeg" /><figcaption>The first working prototype</figcaption></figure><p>Almost immediately, we gained traction with large institutional investors and industry stakeholders. They understood the importance, and inevitability, of big data, but had yet to integrate it into their day to day decision making process. OfficeRank, which was our very first product, showed them how.</p><p>We were lucky. We had clients that saw the same opportunities we saw, which allowed us to build out our product, literally in real time. <strong>Developing and deploying code minutes before presentation was not uncommon and proper CI/CD was still a mythical concept.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YMAwcYZXKaOYb7FHMZHogg.png" /><figcaption>One of the detail pages of our dashboard</figcaption></figure><p>Before we realised, a year had passed. We encountered the same obstacles and opportunities on the paths that every successful startup journeys … running a rapidly expanding digital platform on the same codebase. Yes, 1 codebase. Which of course was harder to maintain as it had organically grown over time and interest in the product kept on growing, which then caused us to develop many more features, handle support requests, do side projects and so on! As a result, the complexity of our infrastructure and code base increased.</p><p>Our tech-team of 3 decided it was time to rebuild our platform and underlying database. At the same time, we decided to expand beyond the Netherlands and into other asset classes.</p><p>Just like any other startup, we were constantly adjusting our product to find the right market fit. From a backend standpoint, this made things tricky, as we were rebuilding not knowing what the future may hold.</p><p>Given our technical background, we stuck to what we knew, putting little focus on defining features or uses cases. We focused on redesigning our database structure from the ground up while sticking to mysql. We decided on <a href="https://laravel.com/">Laravel</a> as our PHP framework and redesigned our UI. We adopted queueing to spread the workload over multiple work instances as the aggregations were out of control due to the growing data sets. The architecture was still a monolith but one that allowed us to grow and build our platform into something that is now handling millions of buildings and serving customers worldwide.</p><p>We went from a team of of 3 to 60 (btw we’re<a href="https://geophy.com/jobs"> hiring</a> ), and we have no intention of stopping. We want to map the world. Rough estimate indicate <strong>4.6B properties worldwide, not including the contextual data element.</strong> Looking ahead, we certainly have our work cut out for us. Looking back though I still wouldn’t change the way we approached the tasks at hand. Our agility is what made this possible: quickly adding features, testing them in the market without trying to build the perfect technical solution. <strong>Just make it work!</strong></p><p>Keep connected, as I’ll detail our first architectural choices next.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=69ba96a8ae1a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geophy-hq/a-startup-journey-part-1-just-making-it-work-69ba96a8ae1a">A Startup Journey Part 1, just making it work</a> was originally published in <a href="https://medium.com/geophy-hq">GeoPhy</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>