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?

  • Sometimes the error message actually describes the issue. You just need to reread it to see the mismatched datatypes. – Vince Nov 16 '16 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 Nov 17 '16 at 1:48
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.

  • 1
    I'm guessing it's sde.ST_GeomFromText – LR1234567 Nov 16 '16 at 21:53
  • The comparison function also needs to be sde.ST_Within (so that the parameters match the function) – Vince Nov 17 '16 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 Nov 17 '16 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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