8 ms·

I have a passion project 4x4anarchy.com that operates with a Python-MariaDB system for querying map data by latitude and longitude, transforming it into GeoJSON

I have a passion project 4x4anarchy.com that operates with a Python-MariaDB system for querying map data by latitude and longitude, transforming it into GeoJSON for map display. The website deals with sizable tables, approximately 1 GB in size. I've made extensive optimizations, relying on well-structured indexes, caching mechanisms, and query optimization to enhance performance. Given these circumstances, how might the incorporation of Julia and some geospatial DB (PostGIS) contribute to further optimizing geospatial data retrieval and presentation, especially when dealing with large datasets and intricate geospatial operations?

benzofuran 3y ago
Cool site! Any chance of a adding a simple KMZ export for offline use for a given area of interest?
- jstrickshire 3y ago
  Yeah, I can do that. Will get to it tomorrow!
  - benzofuran 3y ago
    Awesome - getting KMZs of 4x4 routes is way harder than it should be. All the Colorado data is there but extracting it is challenging.
tony_cannistra 3y ago
Interesting! I work on a very similar product. I don't know Julia well, but I definitely would suggest exploring whether PostGIS can help improve the speed of your DB queries. I'd also consider how you deliver your geospatial data to your clients -- I'm not sure GeoJSON is your best bet. Protobuf tiles might be better for your use-case (e.g. the Mapbox Vector Tiles spec).
- p4ul 3y ago
  I completely agree! It would be hard to overstate the power of PostGIS! For anyone working with GIS data, it's absolutely worth investigating what PostGIS provides and the ease of integration to your existing application!
gabegm 3y ago
It would depend on where most of the processing is happening. PostGIS gives you the benefit of spatial indexes which are extremely performant. I've seen Python GeoSpatial applications taking hours to finish processing which only took a few minutes when shifted onto PostGIS. If you're also doing a lot of processing in Python, exploring other languages could also help. In the case of Julia you get a typed language that's also JIT compiled.
- qfwfq_ 3y ago
  Geopandas has had spatial indexing available for quite a long time... https://geopandas.org/en/stable/docs/reference/sindex.html https://geopandas.org/en/stable/docs/reference/sindex.html I think that the challenge for most is that the PostGIS query planner does the indexing for you in most queries, while a naive all-pairs comparison in geopandas/shapely won't tell you to use the .sindex attribute instead.
mustknow2201 3y ago
I know you said it's a passion project, but you should probably still give the correct OSM attribution https://osmfoundation.org/wiki/Licence/Attribution_Guidelines#OpenStreetMap_Attribution_Guideline https://osmfoundation.org/wiki/Licence/Attribution_Guideline...
- jstrickshire 3y ago
  I appreciate you calling that out, I will get that done.
fiedzia 3y ago
If all you do is "find records within x miles from lat,lon", solr/ES is the best solution. I think it can match a shape too.

benzofuran 3y ago

Cool site! Any chance of a adding a simple KMZ export for offline use for a given area of interest?

jstrickshire 3y ago
Yeah, I can do that. Will get to it tomorrow!
- benzofuran 3y ago
  Awesome - getting KMZs of 4x4 routes is way harder than it should be. All the Colorado data is there but extracting it is challenging.

tony_cannistra 3y ago

Interesting! I work on a very similar product. I don't know Julia well, but I definitely would suggest exploring whether PostGIS can help improve the speed of your DB queries. I'd also consider how you deliver your geospatial data to your clients -- I'm not sure GeoJSON is your best bet. Protobuf tiles might be better for your use-case (e.g. the Mapbox Vector Tiles spec).

p4ul 3y ago
I completely agree! It would be hard to overstate the power of PostGIS! For anyone working with GIS data, it's absolutely worth investigating what PostGIS provides and the ease of integration to your existing application!

gabegm 3y ago

It would depend on where most of the processing is happening. PostGIS gives you the benefit of spatial indexes which are extremely performant. I've seen Python GeoSpatial applications taking hours to finish processing which only took a few minutes when shifted onto PostGIS. If you're also doing a lot of processing in Python, exploring other languages could also help. In the case of Julia you get a typed language that's also JIT compiled.

qfwfq_ 3y ago
Geopandas has had spatial indexing available for quite a long time... https://geopandas.org/en/stable/docs/reference/sindex.html https://geopandas.org/en/stable/docs/reference/sindex.html I think that the challenge for most is that the PostGIS query planner does the indexing for you in most queries, while a naive all-pairs comparison in geopandas/shapely won't tell you to use the .sindex attribute instead.

mustknow2201 3y ago

I know you said it's a passion project, but you should probably still give the correct OSM attribution https://osmfoundation.org/wiki/Licence/Attribution_Guidelines#OpenStreetMap_Attribution_Guideline https://osmfoundation.org/wiki/Licence/Attribution_Guideline...

jstrickshire 3y ago
I appreciate you calling that out, I will get that done.

fiedzia 3y ago

If all you do is "find records within x miles from lat,lon", solr/ES is the best solution. I think it can match a shape too.