Hacker News Reader

Comment by jstrickshire | Hacker News Reader

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?

View on HN

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.

Geopandas has had spatial indexing available for quite a long time...

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.

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).

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!

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_Guideline...

I appreciate you calling that out, I will get that done.

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

Yeah, I can do that. Will get to it tomorrow!

Awesome - getting KMZs of 4x4 routes is way harder than it should be. All the Colorado data is there but extracting it is challenging.

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.

Reply on news.ycombinator.com