Graphql-engine: Using both: geography and geometry types

[asindl]

This is not an issues, but a question:
We have dozens of geospatial tables in our database. The geospatial columns are of geometry type. We would like to use Hasura geospatial "metric" operators like _st_d_within and therefore would need geography types.
Is there yet another way then
a) double the size of our tables by adding a geography column next to the geometry column (pretty much holding exactly the same information as the geometry column)
b) create a view for each table casting to geography in an additional column?

Preferred would be something like casting the geometry column to geography in the Hasura query, but I am not aware if that is possible at all.

[leoalves]

@asindl
Hasura uses PostGIS for geospacial calculations.

You can still use _st_d_within with Geometry types. The difference is the way it calculates the distance.
https://postgis.net/docs/ST_DWithin.html

Geography types the calculations are done in meters. Geometry types the calculations are based on the distance of SRID. SRID is the unit you defined when creating the field.

I created this Heroku container and inserted some data.
https://geometrytypetest.herokuapp.com/console/api-explorer

Try running these queries, in the GraphiQL explorer. They will both return the same result. But the distance I am passing is different as the argument is different.

query {
  #query to get all records 50km from Downtown Ottawa
   geography_table_aggregate(where:{location: {_st_d_within: {distance:50000, from:{type:"Point", coordinates:[-75.692482, 45.425533]}}}}) {      
      aggregate {
        count
      }
    nodes {
        name
      }
    }
  geometry_table_aggregate(where:{location: {_st_d_within: {distance:0.5, from:{type:"Point", coordinates:[-75.692482, 45.425533], crs: {
          type: "name",
          properties: {
            name: "EPSG:4326"
          }
        }}}}}) {
      aggregate {
        count
      }
      nodes {
        name
      }
    }
}

If you need your distance to be calculated in meters, as you said, you have two options.

• Cast the type from Geometry to Geography
• Duplicate the data one column Geography and another one Geometry

For my use case, I have the data duplicated. Storage is cheaper than CPU. But each case is different and you should test to see what best fits your needs.

To cast the type you need to create a view and query the view.

The SQL to create the view is:

CREATE OR REPLACE VIEW "public"."casttype" AS 
 SELECT geometry_table.code,
    geometry_table.name,
    geometry_table.country,
    geometry_table.city,
    geography(ST_Transform(geometry_table.location, 4326)) as location
   FROM geometry_table;

The query would be the same as the Geography one but querying the view.

query {
  casttype_aggregate (where:{location: {_st_d_within: {distance:50000, from:{type:"Point", coordinates:[-75.692482, 45.425533]}}}}) {      
      aggregate {
        count
      }
    nodes {
        name
      }
    }
}

Of course the query that does not cast is faster. Depending on how many records you have in your table the best is to duplicate the data. But it's up to you.

Here is the SQLSTMT that I used to create the test data, in the Heroku example above.
HasuraGeoData.txt

[asindl]

Wow @leoalves! Thanks for such a detailed answer!
As we have dozens of tables with a total of several 100k of (partially complex) geometries we would end up either with pretty much doubling the size of the database or adding a view to each table...
But we know the options to decide between...
It would have been too easy if we had had the opportunity to cast during query time ;-)

Thanks again!

[leoalves]

Hi @asindl

If all your tables have the same structure you can create a function and pass the table name in the args.

For ex:

CREATE OR REPLACE FUNCTION search_cast(table_name text, distance int, point text
RETURNS SETOF geography_table AS $$
BEGIN
    RETURN QUERY 
    EXECUTE 
    '
    SELECT
    code,
    name,
    country,
    city,
    geography(ST_Transform(location, 4326)) as location
    FROM ' || quote_ident(table_name) || '
    WHERE ST_DWithin(location, ST_GeomFromGeoJSON(' || point || ')::geography,' ||  distance || ')'
;

END;
$$ LANGUAGE plpgsql STABLE;

and query like this:

query {
  search_cast(args: {distance: 50000, table_name: "geometry_table2", point: "'{\"type\":\"Point\", \"coordinates\":[-75.692482, 45.425533]}'"}) {
    location
    city
  }
}

[0x777]

It would have been too easy if we had had the opportunity to cast during query time ;-)

@asindl @leoalves We are also open for supporting such functionality natively if you can think of a good syntax for casting the geometry column to geography in the _st_d_within operator for a geometry column.

[asindl]

I am not really familiar with GraphQL, so I am not sure, what a valid syntax could look like.
I don't know, if a syntax like this ... where:{(location:geography): {_st_d_within: {...}) ... would be valid GraphQL.
Otherwise, there could be an optional argument in the _st_d_within operator, forcing the arguments to be casted to geographies/geometries. To be consistent, all spatial arguments needed this argument then.

[leoalves]

@0x777 @asindl

I think cast would be usefull for all types not just geography.

What about

query {
  my_table (cast: {field_name: cast_type}) {

  }
}

and the generated SQL would be something like:

 select field_name::cast_type from my_table

And for more complex casting where the type needs to be transformed, something like:

query {
  my_table (cast:{field_name: {_st_tranform: { this needs more research into the st_transform function}}})
}

[asindl]

I wanted to bring this up again. Is there any further consideration on type casting? Especially with spatial data types?
Or is that not on the roadmap?

[0x777]

Introducing casting across entire types needs a bit more research and use cases. This particular use case can be solved by having a _st_d_within_transform on geometry columns? You'll be able to use it as follows:

some_geometry_col: {
  _st_d_within_transform: {
    using_srid: 4326
    distance:50000
    from:{type:"Point", coordinates:[-75.692482, 45.425533]}
  }
}

Does this proposal makes sense? @asindl @leoalves

[thangtq-intelads]

how to query distance from geog (a column with type geography) to $loc (a parameter) like sql below
select st_distance(geog, $loc) from geo_table;

can you help me?

[dsandip]

@thangtq-intelads Check out this example in the docs.

[mantagen]

Geography types the calculations are done in meters. Geometry types the calculations are based on the distance of SRID. SRID is the unit you defined when creating the field.

@leoalves It seems like the SRID can be defined on a record by record basis as opposed to when creating the field.

Having previously been unable to use metres in _st_d_within in a graphql query (without previously writing an sql VIEW or FUNCTION) I tried the following: I created field type Geometry in the hasura console, and inserted a record with the following data in that field

{
      type: "Point",
      coordinates: [-0.1082163, 51.5685594],
      crs: {
        type: "name",
        properties: {
          name: "urn:ogc:def:crs:EPSG::4326"
        }
      }
} 

After this, _st_d_within was calculated using metres. As an experiment I created another record without specifying the crs, and _st_d_within no longer worked, returning an error "Operation on mixed SRID geometries".

I'm new to postgis and geometry/geography types, I've got a few questions (which I feel are relevant to and might help to progress this issue):

1. What is the default SRID for a Geometry in hasura when no crs specified in the GeoJSON?
2. It says here

Standard geometry type data will autocast to geography if it is of SRID 4326.

does that mean you can simply store your locations as 4326 and no need to write separate logic to cast?

1. Should it be possible to insert data (one value with specified crs and one without) which in turn renders the _st_d_within function useless? Perhaps this is more a point for postgis itself, as it's specified here

For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID.

Anyway, hasura's awesome

[leoalves]

Hi @mantagen ,

What is the default SRID for a Geometry in hasura when no crs specified in the GeoJSON?

Hasura won't set a default SRID for you. It will just convert you GeoJSON to Geometry, using ST_GeomFromGeoJSON, in the generated SQL. If you don't specify the crs, your record won't have the SRID data. And your queries could become inconsistent. That's why it's better to set the SRID when creating the column.
I can't think of a reason you would want to have different SRIDs in the same column. It's reasonable to store all geometries with the same SRID.

Standard geometry type data will autocast to geography if it is of SRID 4326.

My understanding from that quote is that in Geography field types, if you insert a value that is in the Geometry format, without specifying a SRID, it will default to 4326. But only in Geography types. Not in Geometry.

Should it be possible to insert data (one value with specified crs and one without) which in turn renders the _st_d_within function useless? Perhaps this is more a point for postgis itself, as it's specified.

When you create the field with a specified SRID, you can avoid having issues where values returned have different SRIDs. If you need to store values with different SRIDs, you will need to create a View or Function to transform the data to a common SRID.

After this, _st_d_within was calculated using metres.

Are you sure the calculation was done in meters? All my Geometry fields are 4326, and the _st_d_within in the Geometry type is not done in meters. If I do the same query in the Geometry and Geography types, with the same data, the results are different.

[asindl]

Introducing casting across entire types needs a bit more research and use cases. This particular use case can be solved by having a _st_d_within_transform on geometry columns? You'll be able to use it as follows:

some_geometry_col: {
  _st_d_within_transform: {
    using_srid: 4326
    distance:50000
    from:{type:"Point", coordinates:[-75.692482, 45.425533]}
  }
}

Does this proposal makes sense? @asindl @leoalves

This _st_d_within_transform function wouldn't necessarily tell me, what I am transforming to. The geometry/geography handling in PostgreSQL is not really transforming anything. It is using the exact same coordinates but by telling PostgreSQL if the coordinates represent a geography or geometry, it tells whether PostgreSQL uses cartesian (geometry) or spherical (geography) algorithms,
I think, calling it a "cast" would be more appropriate: we would cast a geometry to a geography in order to tell PostgreSQL to use spherical algorithms.

In PostgreSQL for example, casting is done by using the :: operator.
geom::geography

[asindl]

Hi there,
I actually found a solution for this, which is working nicely for me:

If you have a geometry column in your database and would like to support geography-based queries as well, it is proposed to create a geography index on this geometry column in PostgreSQL. When you run a SQL query and cast the geometry to a geography then PostgreSQL uses the geography index. This is an elegant way to support geography and geometry searches in parallel while not creating a dedicated geography column (doubling the size of the geometries in the end). See this entry here: https://gis.stackexchange.com/questions/247113/how-to-properly-set-up-indexes-for-postgis-distance-queries/247131

The great thing now in Hasura: it lists this index in the explorer view just like a table but named {tablename}_geography. You can now run all queries on this "table" using geography types!

[friend0]

@asindl I'm trying to do this now, but don't see the new indexed table in the explorer. Any ideas on what I've created my table w/ geom column, added a GIST index on the column - any idea what I'm missing?
I'm on Hasura beta.2

[asindl]

Yeah, @friend0, I double checked and found, that while I was out of office a couple of days, the team has added views on the geometry tables. This is actually what realize the geography handling in Hasura. So it is not the index as I mentioned before, but the views....

Sorry for the confusion.

It basically looks like this:

CREATE VIEW schema.geoTable_geography AS
 SELECT     geoTable.geom::geography AS geog,
-- other parameters...
   FROM schema.geoTable;

Still, the index on the geography is actually required to speed up calls that use geography searches then.

CREATE INDEX geoTable_idxbounds_geog
    ON schema.geoTable USING gist
    (geography(geom))
    TABLESPACE pg_default;

[friend0]

@asindl got it, I was eventually able to get it to work by running operations against a geography column instead. I was a little skeptical that there would be that much magic just from an index, but it seemed worth a shot 😅

[lexi-lambda]

Supporting arbitrary casts everywhere seems hard due to syntactic and type system restrictions of GraphQL, but I don’t see any reason why we can’t support arbitrary casts in a more limited sense inside where expressions. We could extend the grammar of *_comparison_exp types to include additional members:

type geometry_comparison_exp {
  ...
  _cast_to_text: text_comparison_exp
  _cast_to_geography: geography_comparison_exp
}

…which would allow you to use the following where restriction:

some_geometry_col: {
  _cast_to_geography: {
    _st_d_within: {
      using_srid: 4326
      distance: 50000
      from: { type: "Point", coordinates: [-75.692482, 45.425533] }
    }
  }
}

[lexi-lambda]

Doing truly arbitrary casts in where expressions turned out to be slightly more effort than I had hoped, and it’s not immediately clear to me if they’re actually useful outside of this feature, so I’ve started by hardwiring in support for casting between geometry and geography types. I’ve opened #2495, which implements casts with following syntax:

query cities_near($point: geography!, $distance: Float!) {
  cities(
    where: {location: {
      _cast: {geography: {
        _st_d_within: {from: $point, distance: $distance}
      }}
    }}
  ) {
    name
  }
}

I believe that should accommodate your use case, but let me know if something’s missing. If you want to try it out, you can try the hasura/graphql-engine:pull2495-1f48fba4 Docker image.

[leoalves]

@lexi-lambda,

Nice, that was quick.

I did a quick test, and everything seems to be working.

The only thing I noticed is that the geometry_cast_exp type expects a geography_comparison_exp, which has the _cast on it. If it could expect a geography_comparison_exp_without_cast I think it would look cleaner.

Not sure if it is worth the trouble. But that is the only thing I noticed.

Thanks for all your hard work on this. Looks awesome.

[asindl]

I finally tried out the cast feature the first time. I am using Hasura v1.0.0-beta.3. I copied and pasted the example from the documentation (https://docs.hasura.io/1.0/graphql/manual/queries/query-filters.html#cast-a-field-to-a-different-type-before-filtering-cast) and adapted table and column names to my database.
Unfortunately, I get an error message:
"message": "field \"_cast\" not found in type: 'geometry_comparison_exp'"

[ThiyagarajRKO]

@0x777 @asindl

I think cast would be usefull for all types not just geography.

What about

query {
  my_table (cast: {field_name: cast_type}) {

  }
}

and the generated SQL would be something like:

 select field_name::cast_type from my_table

And for more complex casting where the type needs to be transformed, something like:

query {
  my_table (cast:{field_name: {_st_tranform: { this needs more research into the st_transform function}}})
}

Could u tell me how to convert integer to string using cast in Hasura JSON format. Please