PostGIS operators

[jney]

just wondering if there is any postgis support planned ? or already working ?

thanks

[begriffs]

Nothing yet, but sounds like a good idea.

Tell me more about how you would like to do spatial queries. What kind of interface would be convenient for you?

[jney]

to be totally honest i haven't really thought how it should look like.

a postgis query looks like :

SELECT region, ST_Union(the_geom)
FROM usa_states
GROUP BY region

the point is to handle sql "functions", is there a postgrest way to call AVG, SUM or such functions ?

[begriffs]

You can make a view which uses these functions and the view will be accessible to the client just like a table would be.

[parauchf]

I don't have an immediate need, but I can see a use case for some additional filter operators. E.g., I want to return all records with location within 500m of a point.

[begriffs]

We could add some of the geospatial operators as valid filters in the URL, although there are a few problems. One is that many of the operators use an ampersand which would need to be urlencoded. Another is that operators like <-> are functions and we haven't devised a way yet to express things like foo <-> bar < 20 in the URL filter. Maybe this leads back to one of the first reported issues, #114. A stored procedure could take a point argument and return a view of some table filtered by postgis.

[begriffs]

For now try making and calling a stored procedure to do what you need. http://postgrest.com/api/reading/#stored-procedures

[chrismayer]

I also have the opinion that it would be great thing to have PostGIS support in PostgREST. I also go along with @begriffs that a lot of things can be achieved by calling stored procedures, whereas some spatial filter operators (as suggested by @parauchf) would also be nice to have.

But I think it would be a great benefit to have a built in support for a (Web)GIS compatible output format such as GeoJSON. GeoJSON is a standardized format in the GIS world which is often used as a return value of HTTP calls in Webmapping applications. So maybe by setting the HTTP-header Accept to an according value might let the PostgREST call to a table return a GeoJSON FeatureCollection.

What do you think about this?

Some more hints on this: PostGIS has a function ST_AsGeoJSON, which might be useful for this. To pack a table with features in a GeoJSON FeatureCollection the following SQL could be used

SELECT row_to_json(fc)
 FROM (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
 FROM (SELECT 'Feature' As type
    , ST_AsGeoJSON(my_geo_table.geom)::json As geometry
    , row_to_json((gid, code)) As properties
   FROM my_geo_table  ) As f )  As fc;

[calebmer]

@chrismayer I would like to see PostgREST support for this, but I don't think any potential contributors are interested in tackling this problem. So three questions:

1. How big of a footprint does it leave on PostgREST core?
2. Could this be implemented in a layer outside of PostgREST?
3. Would you be willing to implement this? (Haskell isn't that bad to learn 😊)

One of the 6 REST principles is Layered System. If this support could be implemented in a layer that would be the best case scenario.

[chrismayer]

Hi @calebmer,

thanks for your sharing your thoughts.

How big of a footprint does it leave on PostgREST core?

This i cannot answer since I did not do any work in the PostgREST core. Maybe a core dev can tell us more here?

Could this be implemented in a layer outside of PostgREST?

I guess it would be possible to implement this outside of PostgREST core. So this would be a kind of extension / plugin / or similar, right? Maybe some hints by the pros how to achieve this would be very good.

Would you be willing to implement this?

I am not sure if I am the right guy for this. I am more a user than a dev of PostgREST and in general I haven't wrote a line of Haskel yet. I can support with my GIS / Geo knowledge.

[gorbypark]

I would also be very, very interested in GeoJSON/PostGIS support. I'd love to contribute but my Haskel skills are not up to snuff...is there any way I could offer a small bounty to any of the core devs for this feature? I was thinking of something pretty simple, such as a "selector" option to output as geojson http://url.com/items?geomcol=geom (where geom is the column with geometry). Even these core features would make it usable for me. Some of the more exotic PostGIS functions could be added later.

[begriffs]

One option to add a geojson column in the output is by using a computed column as described in https://postgrest.com/en/v0.4/api.html#computed-columns

I think it would output just what you want, but it would require writing similar computed column code for each table for which you want to include geojson.

[begriffs]

@gorbypark want to give the computed column approach a try and let me know how it works for you?

[gorbypark]

@begriffs I'll give it a go sometime this week, hopefully. My only concern is that I have a large, ever changing dataset (user created tables and columns). I will have to see if I can automate creating computed columns somehow...I haven't had a chance to even read the blurb about it yet, so that may be easier or harder than I'm imagining!

[gorbypark]

@begriffs So, I had a few minutes to put together a SQL query that outputs valid GeoJSON. I'm not quite sure how to route filters into the SQL query. Any hints? I just slapped $1, $2, $3 ($1= columns to select, $2 = column, $3 = query) as a guess...

CREATE FUNCTION geohydrants(select, filterColumn, filter) RETURNS text AS $$

SELECT jsonb_build_object(
    'type',     'FeatureCollection',
    'features', jsonb_agg(features)
)
FROM (
  SELECT jsonb_agg(jsonb_build_object(
    'type',       'Feature',
    'geometry',   ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,
    'properties', to_json(row)
  )) FROM (SELECT $1 FROM schema.hydrants WHERE $2 = '$3' ) row) features;

$$ LANGUAGE SQL;

[palmerj]

Why has this issue been closed, it's still unresolved

[palmerj]

I think for PostgREST to be useful for PostGIS developers it needs to detect if any of the object output column datatypes are geometry and convert them to an appropriate text representation. e.g

• text/csv then use the function ST_AsText()
• application/json use ST_AsGeoJSON()

Yes a stored DB functions could be used, but that means all database tables/views objects with geometries outputs will not work as expected. Also the user of the API can ask for CSV or JSON output it can't be nicely dealt with at the database level.

[begriffs]

@palmerj fair point, I'll re-open the issue.

Maybe the best way to focus the issue is to post SQL for creating a table with columns of the desired postgis types, include example data, and then the desired output when issuing a GET request on that table. I can then use that information to create a feature test and see how far off we are and how to fix it.

[palmerj]

Ok thanks, will do

[palmerj]

Also I also think (as commented above) it would be good to support some common geometry operators. At least intersects, if not all of the standard geometry binary predicates. e.g:

ST_Equals ST_Disjoint ST_Touches ST_Contains ST_Intersects ST_Within ST_Crosses ST_Overlaps

To support these functions I think at least ST_MakeEnvelope(xmin,ymin,xmax,ymax[,srs]) and ST_GeomFromText(wkt,[srs]) geometry constructor functions are required as well.

[ruslantalpa]

@palmerj @begriffs i don't think postgrest should be detecting any column type (for now) in order to format it in some way. The users have the tools to format them as needed. As i've said in other places, postgrest should not be used to expose tables directly, but views that draw data from those tables. Considering that, it means the users can have views with columns formatted as needed, independent of the column type in the table. You can even have one column in the view that can output different formats based on the csv/json thing using https://postgrest.com/en/v0.4/api.html#computed-columns because now in that function you can read the request headers and make a decision on how to format the output.

As for operators, i agree it would be a good idea to have some support but we need to think carefully about this since they are not really operators but functions and they can't be easily supported by our current code just by adding one line.

[begriffs]

Ruslan is right in that creating a computed column for a table is really easy -- you just make a function. Then the table gets a new column that you can select as part of the table. No extra views even needed for that. Now if you have a lot of geometry columns you would have to make a bunch of these functions to get the columns you need. Might get repetitive.

I am curious what the default output format is for geometry columns. Depending on what that is, we should ask if it is ever desirable. If the default serialization is garbage that nobody would consume then auto-converting it kind of makes sense. Same as if the default output format of an int column was a binary string, I would consider that a deficiency in postgrest.

[palmerj]

@palmerj @begriffs i don't think postgrest should be detecting any column type (for now) in order to format it in some way. The users have the tools to format them as needed.

This is true, but IHMO to approach of creating specific pl/PgSQL function per view/table that has to read a PostgreSQL GUC setting is cumbersome, as @begriffs said it's repetitive. One of looking at this issue is we have a database application that we might potential expose 100's of table and PgSQL functions APIs that return geometry types. Under this solution I would need to write and maintain 100's of functions like this:

CREATE FUNCTION geom_geojson(foo.bar) RETURNS text AS $$
  SELECT ST_AsGeoJSON($1.geometry);
$$ LANGUAGE SQL;

As i've said in other places, postgrest should not be used to expose tables directly, but views that draw data from those tables. Considering that, it means the users can have views with columns formatted as needed, independent of the column type in the table.

Ok I agree with this, especially when dealing with versioning an API. BTW I don't see any documentation about best approaches for handling versioning of the API. Can you point me in the right place? This seems to be out of date https://github.com/begriffs/postgrest/wiki/API-Versioning and I can't find anything in the official docs.

You can even have one column in the view that can output different formats based on the csv/json thing using https://postgrest.com/en/v0.4/api.html#computed-columns because now in that function you can read the request headers and make a decision on how to format the output.

Yes this would at mean that only one function would be required per table if CSV and GeoJSON was required. For this functionality I assume you are talking about this recent merged PR which is not part of the current 0.4 release? https://github.com/begriffs/postgrest/pull/849. It would be nice if there were some docs for how to use this via current_setting() because I'm not sure what GUC parameter I need to use.

[treuherz]

The canonical form returned by PostGIS is a hex representation of EWKB, which is a superset of the OGC-standard Well-Known Binary. They recommend not relying on that feature, though, as it may change if the OGC changes the standard to support 3D geometries (more info here https://postgis.net/docs/using_postgis_dbmanagement.html#EWKB_EWKT). WKB is widely parseable, but I don't know how an interface expecting WKB would deal with an EWKB input, so outputting that might not be desirable.

[CMCDragonkai]

I'd suggest considering raster support as well.

[amaury1093]

@begriffs Ok for adding computed columns, pretty easy. However how would I do inserts?

This is an example query:

INSERT INTO countries ("id","the_geom") VALUES (DEFAULT,ST_GeomFromGeoJSON('{some_geo_json_object}'))

Would be nice to be able to POST /countries with a GeoJSON object.

[ruslantalpa]

before insert trigger NEW.the_geom = _GeomFromGeoJSON(NEW.the_geom)

[rastermanden]

Paul Ramsey made this post about returning af geojson Feature collection:

CREATE OR REPLACE FUNCTION rowjsonb_to_geojson(
  rowjsonb JSONB, 
  geom_column TEXT DEFAULT 'geom')
RETURNS TEXT AS 
$$
DECLARE 
 json_props jsonb;
 json_geom jsonb;
 json_type jsonb;
BEGIN
 IF NOT rowjsonb ? geom_column THEN
   RAISE EXCEPTION 'geometry column ''%'' is missing', geom_column;
 END IF;
 json_geom := ST_AsGeoJSON((rowjsonb ->> geom_column)::geometry)::jsonb;
 json_geom := jsonb_build_object('geometry', json_geom);
 json_props := jsonb_build_object('properties', rowjsonb - geom_column);
 json_type := jsonb_build_object('type', 'Feature');
 return (json_type || json_geom || json_props)::text;
END; 
$$ 
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

--Voila! Now we can turn any relation into a proper GeoJSON “Feature” with just one(ish) function call.

SELECT rowjsonb_to_geojson(to_jsonb(mytable.*)) FROM mytable;  

Could we have a url param like &format=geojson

[steve-chavez]

@rastermanden I think we could be more in line with HTTP semantics and use Accept: application/vnd.geo+json to request geojson.

Supposing we manage to accommodate that function you shared as inline sql, how could we handle the name of the geometry column to choose? What if the table has more than one geometry column?

[rastermanden]

Accept: application/vnd.geo+json would most elegant but desktop GIS users will probably not have the option to set those headers in their environment.

Regarding geometry columns i'm not sure. Carto (Carotodb) use to rely on a fixed column name: "the_geom". Maybe not the best solution but has been working for years

[leblancfg]

Related project: https://github.com/tobinbradley/dirt-simple-postgis-http-api