1

If I run the following query in PostgreSQL 9.2 I get the geometry:

select st_geomfromtext('POLYGON((-123.13974380493164 49.258142464379425,-123.11695575714111 49.25450118750378,-123.11914443969727 49.24965507167121,-123.13845634460449 49.25158797259088,-123.14326286315918 49.254669252349466,-123.14257621765137 49.25595773048919,-123.13974380493164 49.258142464379425))')
from park

However, if I try to put the st_geomfromtext() into an st_within, like this:

select objectid
from park
where st_within(shape,st_geomfromtext('POLYGON((-123.13974380493164 49.258142464379425,-123.11695575714111 49.25450118750378,-123.11914443969727 49.24965507167121,-123.13845634460449 49.25158797259088,-123.14326286315918 49.254669252349466,-123.14257621765137 49.25595773048919,-123.13974380493164 49.258142464379425))'),4326) = 't'

I get:

function st_within(sde.st_geometry, geometry, integer) does not exist...You might need to add explicit type casts

Looks like I need to cast my WKT as st_geometry somehow. Any suggestions?

Improve this question
2
  • Sometimes the error message actually describes the issue. You just need to reread it to see the mismatched datatypes.
    – Vince
    Commented Nov 16, 2016 at 23:54
  • Even solely within PostGIS, you've got a syntax error, as well -- the "4326" is the second parameter of the ST_GeomFromText function, but you have it as the third parameter of ST_Within (paren alignment issue). Change ))'),4326) to ))',4326))
    – Vince
    Commented Nov 17, 2016 at 1:48

1 Answer 1

Reset to default
3

You are mixing up PostGIS spatial columns and sde spatial columns. Your 'shape' column is using the Esri spatial type. Your ST_GeomFromText() function is creating a PostGIS spatial type. And the ST_Within() function is complaining that you're mixing types. Figure out what the Esri equivalent to ST_GeomFromText() is, and use that, so your types match.

Improve this answer
3
  • 1
    I'm guessing it's sde.ST_GeomFromText
    – Regina Obe
    Commented Nov 16, 2016 at 21:53
  • The comparison function also needs to be sde.ST_Within (so that the parameters match the function)
    – Vince
    Commented Nov 17, 2016 at 1:42
  • It SHOULD be sde.st_geomfromtext, but my PostegreSQL 9.2, PostGIS 2.0.6 does not have such a function, on st_geomfromtext().
    – Jim
    Commented Nov 17, 2016 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.