Example / Docs for working with PostGIS

[Nosferican]

Would be great to know how to work with geometries in Julia. For example,

data = execute(conn,
               """
               SELECT ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS geom
               FROM tbl;
               """,
               not_null = true) |>
    DataFrame

Which package in Julia would be recommended to parse the String to a geometry in Julia?

[iamed2]

You would probably want to use https://github.com/JuliaGeo/LibGEOS.jl

I would recommend using the PostgreSQL function ST_AsEWKT in your query and then parsing with readgeom from LibGEOS.jl. Or you could use ST_SetSRID(ST_MakePoint(lon, lat), 4326)::bytea in your query and parse with readgeom.

[iamed2]

This would be a good example case to talk about how to do type conversions in LibPQ.jl

[kengruven]

Hello from 2025! I was playing around with PostGIS -> Julia this week.

My initial assessment is that LibGEOS is not terribly well documented (especially the I/O parts), and LibGEOS.jl is mostly an (undocumented) automatic wrapper for the C++ code. I'm sure someone smarter than me could make it work, and I hope they document it, because I could not.

Fortunately, there is an easy solution for many use cases: SELECT ST_AsGeoJSON(g) in your query, and then you get back nice simple JSON. For example, I only wanted to read a PointZ (from PostGIS) into a Point (from GeometryBasics), so I could:

using GeometryBasics
Base.parse(::Type{Point}, pqv::LibPQ.PQValue) = Point(JSON3.read(LibPQ.string_view(pqv))[:coordinates]...)

result = execute(conn, "SELECT ST_AsGeoJSON(position) as position FROM table;";
                 column_types=Dict(:position=>Point))

There's also a GeoJSON.jl library which may be helpful, but GeoJSON is so simple already that it's easier for me to parse the JSON use fetch the values I want directly.