2

I am running this query:

SELECT source,ST_AsText(ST_ClosestPoint(roads.geom,ST_GeomFromText('POINT(22.256  39.569)'))) 
FROM roads 
ORDER BY ST_Distance(roads.geom,ST_GeomFromText('POINT(22.256 39.569)')')) 
LIMIT 1;

I have followed this answer and reprojected my table geom_ways in my database to 4326 which is the SRID of geometry points but the query will not run because there are not matching geometries?

Community
  • 1
drizo
  • 427
  • 4
  • 12

1 Answers1

4

ST_GeomFromText('POINT(22.256 39.569)') is not going to give you a geometry in EPSG:4326.

From the documentation:

There are 2 variants of ST_GeomFromText function, the first takes no SRID and returns a geometry with no defined spatial reference system. The second takes a spatial reference id as the second argument and returns an ST_Geometry that includes this srid as part of its meta-data. The srid must be defined in the spatial_ref_sys table.

So you probably want to do: ST_GeomFromText('POINT(22.256 39.569)', 4326)

Basically, WKT doesn't have the notion of a spatial reference.

You can also generate a specific SRID using EWKT, but the two-argument form is usually easier. In EWKT it would be: ST_GeomFromEWKT('SRID=4326;POINT(22.256 39.569)')

BradHards
  • 12,881
  • 2
  • 37
  • 70
  • Thanks for answering. Although I used EWKT the query runs but the results I get are so wrong.In my database it says that SRID is 4326 but the lon lats are like this: LINESTRING(0.00021071 0.00033690,0.0002107132 0.00033690) when they should be the geometry point I mentioned above, POINT(22.256 39.569). Why would this be? – drizo Nov 15 '14 at 11:42
  • Okok seems like I run weird data transofrmations for my database.I dropped it and inserted it again and it has proper SRID foramt and combined with the 'trick' you showed me it works! Thank you for your answer! You saved me time! – drizo Nov 15 '14 at 12:12