You want a data structure like a range tree or a k-d tree. An SQL database is a wrong tool for the job if you care about performance. For me PGSQL was the only one capable to do this reliable. When you do that within a second, you got yourself a good database. No more then a thousand points of interest, lets say. Make a small application that will show a map, put 100 million points of interest on that map, that are contained in the table we talk about, and now as you scroll the map, select the middle of view as your circle and select on a small radius only those points of interest inside that radius. >And before starting to bash me, please do this. I do think that postgres' indexing support (gist, gin in particular) makes it easier to add support for indexing types of data that are harder to index in many other databases. > then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)? I'm not all saying that only postgres has geospatial indexing support (although postgis is pretty good) - that would be ridiculous. And then subsequently to your statement about vectorized columnstores. Which I still think is not a meaningful alternative. > Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. Why are you talking about not using geospatial features? I was just responding to your > If you're not using geospatial features, finding all polygons in a table that overlap with one specified in a query. I mean, just about any geospatial query? E.g. For non-trivial applications, the real world will eventually produce data that inadvertently executes a denial of service attack on naive geospatial databases. Geospatial index performance is very sensitive to temporal and geometry distribution, and the performance variance can be extremely high. They deprecated that indexing feature in their database engines a long time ago for good technical reasons that apply to current open source implementations. The modern version was created in the 1990s and implemented by big database companies like Oracle and IBM (based on work in the 1960s and '70s). Geohashing has a lot of weaknesses and limitations, not really recommended unless your use case is very simple and not primarily geospatial in nature. Most developers are unaware of just how broken most half-assed implementations are the databases do just enough to check a box but not actually be useful if you are doing real geospatial work. This is a very complex and nuanced piece of mathematics and software engineering work that requires writing a massive amount of code, even most closed source databases don't do a real implementation due to the difficulty. For me PGSQL was the only one capable to do this reliable.įor true geospatial data models that correctly implement most geospatial predicates and operators your only practical option is PostGIS or Oracle Spatial. The only one was PostgreSQL.Īnd before starting to bash me, please do this. Not Maria, not Access, not Oracle, not NoSQL, not MSSQL, not MySQL - all of them failed. Out of my tries, my clients tries, and my friends tries, only one DB was up to this task. ![]() And when you have a table with 100 million rows in it, and an application is not loading data from that table to show it to its users in the same second, you already lost the battle. My clients wanted to have data in front of their eyes the same second when they clicked the button. Nothing else mattered, not license price, not whistles and bells, not hype. ![]() For most of the projects where the DB really mattered, throughout my 10+ freelancer carrier, it came down to one thing that client really cared about.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |