Postgis Query ST_Intersects with GeoJSON (jsonb)

Question

Given a PostgreSQL 9.4 jsonb column 'location' with content like this:

{"type": "FeatureCollection","features": [
  {
    "properties": {},
    "geom": "01030000000100000004000000000000000000244000000000000049400000000000002840000000000000494000000000000026400000000000004A4000000000000024400000000000004940",
    "type": "Feature",
    "geometry": {"type": "Polygon", "coordinates": [[[10,50],[12,50],[11,52],[10,50]]]}             
  },
  {..},
  {..}
]}

To retrieve the feature key 'geom' I use this query:

SELECT geom
FROM (
  SELECT jsonb_array_elements(t.location -> 'features') -> 'geom' AS geom
  FROM my_object t) AS g
WHERE geom IS NOT NULL;

Works. But now I would like to do a Postgis ST_Intersects query like this:

SELECT ST_Intersects(g.geom, ST_GeomFromText('POINT(11 51)'))
FROM (
  SELECT t.iid, (jsonb_array_elements(t.location -> 'features') -> 'geom') AS geom
  FROM my_object t) AS g;

Doesn't work because g.geom is delivered as jsonb: function st_intersects(jsonb, geometry) does not exist. I tried casting to text, the error then is function st_intersects(text, geometry) is not unique. How I have to deal with jsonb results as input for Postgis functions?

Answer 1

First of all, the function jsonb_array_elements() returns a set so you should it as a table function in a FROM clause. Secondly, you should get the text representation of the jsonb field with the ->> operator and then cast that to geometry which you then use in a PostGIS function.

To retrieve the geometries that are not NULL:

SELECT f.value ->> 'geom' AS geom
FROM my_object t
JOIN LATERAL jsonb_array_elements(t.location -> 'features') f ON true
WHERE f.value -> 'geom' IS NOT NULL;

To do the intersect:

SELECT t.iid, ST_Intersects((f.value->>'geom')::geometry, ST_GeomFromText('POINT(11 51)'))
FROM my_object t
JOIN LATERAL jsonb_array_elements(t.location -> 'features') f ON true;

You probably want to add some properties to the select list such that you can distinguish between multiple geometries for each row from your table.

Answer 2

To create a postgis geometry type from the geojson you can use the function ST_GeomFromGeoJSON

For example:

SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}');