Using geographic objects as properties with PostGIS

[pdpotter]

Is there a way to add PostGis data as property to a node (e.g., a point geometry)?

I've tried:

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: ST_SetSRID(ST_MakePoint(-71.10, 42.32),4326)}) return n
$$) as (a agtype);
ERROR:  function ag_catalog.age_st_makepoint(agtype, agtype) does not exist
LINE 2: ... (n:Test {id: 1, location: ST_SetSRID(ST_MakePoint(-71.10, 4...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: public.ST_SetSRID(public.ST_MakePoint(-71.10, 42.32),4326)}) return n
$$) as (a agtype);
ERROR:  function public.ST_MakePoint(agtype, agtype) does not exist
LINE 2: ...1, location: public.ST_SetSRID(public.ST_MakePoint(-71.10, 4...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: public.ST_SetSRID(public.ST_MakePoint(-71.10::float, 42.32::float),4326)}) return n
$$) as (a agtype);
ERROR:  ag function does not exist
LINE 1: SELECT * FROM cypher('testgraph', $$
                                           ^
DETAIL:  agtype_typecast_float(1)

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: public.ST_GeomFromText('POINT(-71.10, 42.32)', 4326)}) return n
$$) as (a agtype);
ERROR:  function public.ST_GeomFromText(agtype, agtype) does not exist
LINE 2: ...E (n:Test {id: 1, location: public.ST_GeomFromText('POINT(-7...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1, location: public.ST_GeomFromText('POINT(-71.10, 42.32)'::text, 4326)}) return n
$$) as (a agtype);
ERROR:  typecast 'text' not supported
LINE 1: SELECT * FROM cypher('testgraph', $$
                                           ^

Additional information: the indivial extensions (age, postgis) do work:

SELECT * FROM cypher('testgraph', $$
    CREATE (n:Test {id: 1}) return n
$$) as (a agtype);
                                     a                                      
----------------------------------------------------------------------------
 {"id": 1688849860263978, "label": "Test", "properties": {"id": 1}}::vertex
(1 row)

SELECT ST_SetSRID(ST_MakePoint(-71.10, 42.31),4326) as location;
                      location                      
----------------------------------------------------
 0101000020E61000006666666666C651C048E17A14AE274540
(1 row)

SELECT PostGIS_Version();
            postgis_version            
---------------------------------------
 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

[jrgemignani]

Sorry, about the late response, I had responded but, not to this thread. From my April 2nd response -

When a function isn't prefaced with the schema in the cypher function call, the transform logic will see it as a cypher function and prepend age_ to the name and then add the schema ag_catalog. This avoids naming clashes and helps with understanding where the system is looking for something. So, if you want to use a specific PG or other user function, you need to specify the specific schema that it is in. When a schema is provided, the transform logic no longer looks for it in ag_catalog with a prepended age_. The function call transforms, inside the cypher function call, recurse through the arguments. So, nested calls will be processed by AGE in the above manner.

This logic may, or may not change in the future. The issue here is that the PG routines that search for the function will exit if one is not found. So our code - unless we add in more of PG's code and modify it - will never get a second chance to process it for additional matches. So, we had to find a happy middle ground. Unfortunately, some of PG's errors are vague. Was it not found at all? or just not one that matches the arguments? Only the debugger can tell you.

For this particular issue, this means that any non-AGE function needs its schema name added if it appears in the cypher function command. As an example, for PG's sqrt(4), it would be pg_catalog.sqrt(4).

Additionally, any function argument inside the cypher function command will be transformed through AGE. These are not the same typecasts as PG's typecasts. So, typecasts like 4::float, will be translated as some number into agtype float, not PG's float. We plan on adding ones like ::pg_float shortly. There is currently an exception to this, and that is due to an implicit cast that is going to be removed and replaced by an explicit cast. There is an implicit cast to float, that will be removed shortly, so if everything is correctly named, what you have above should work - for items that can be cast to a float.

As an example -

psql-11.5-5432-pgsql=# SELECT * from cypher('test', $$
RETURN pg_catalog.sqrt(pg_catalog.sqrt(pg_catalog.sqrt(256))) 
$$) as (result agtype);
 result
--------
 2.0
(1 row)

Hopefully this helps. Unfortunately, I am unable to get Postgis installed and therefore I can't debug it further at this time.

[jrgemignani]

As of the current release, the implicit cast to float has been removed. Additionally, casts have been added to cast an agtype value out to a PG type for the following types: bigint and float8

Examples:

psql-11.5-5432-pgsql=# SELECT * from cypher('G', $$
RETURN pg_catalog.sqrt(pg_catalog.sqrt(pg_catalog.sqrt(256)))
$$) as (result agtype);
ERROR:  function pg_catalog.sqrt(agtype) does not exist
LINE 2: ...ETURN pg_catalog.sqrt(pg_catalog.sqrt(pg_catalog.sqrt(256)))
                                                                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
psql-11.5-5432-pgsql=#

psql-11.5-5432-pgsql=# SELECT * from cypher('G', $$
RETURN pg_catalog.sqrt(pg_catalog.sqrt(pg_catalog.sqrt(256::pg_bigint)))
$$) as (result agtype);
 result
--------
 2.0
(1 row)

psql-11.5-5432-pgsql=# SELECT * from cypher('G', $$
RETURN pg_catalog.sqrt(pg_catalog.sqrt(pg_catalog.sqrt(256::pg_float8)))
$$) as (result agtype);
 result
--------
 2.0
(1 row)

[jrgemignani]

I should note that for use with an indirection, it needs to be done this way -

psql-11.5-5432-pgsql=# SELECT * FROM cypher('G', $$ MATCH (u:numbers) RETURN u $$) AS (result agtype);
                                         result
-----------------------------------------------------------------------------------------
 {"id": 1407374883553281, "label": "numbers", "properties": {"number": 3.14159}}::vertex
 {"id": 1407374883553282, "label": "numbers", "properties": {"number": 4}}::vertex
 {"id": 1407374883553283, "label": "numbers", "properties": {"number": 25}}::vertex
(3 rows)

psql-11.5-5432-pgsql=# SELECT * FROM cypher('G', $$ MATCH (u:numbers) RETURN pg_catalog.sqrt(u.number) $$) AS (result agtype);      ERROR:  function pg_catalog.sqrt(agtype) does not exist
LINE 1: ...r('G', $$ MATCH (u:numbers) RETURN pg_catalog.sqrt(u.number)...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
psql-11.5-5432-pgsql=# SELECT * FROM cypher('G', $$ MATCH (u:numbers) RETURN pg_catalog.sqrt((u.number)::pg_float8) $$) AS (result agtype);
     result
-----------------
 1.7724531023415
 2.0
 5.0
(3 rows)

psql-11.5-5432-pgsql=#

Sorry for all the edits.