Postgis : ERROR: parse error - invalid geometry

Question

I'm using POSTGIS="2.0.1 r9979" and facing following error :

ERROR:  parse error - invalid geometry
HINT:  "POINT(18.570275,7" <-- parse error at position 17 within geometry
CONTEXT:  SQL function "st_dwithin" during inlining

while trying sql :

 SELECT addressid FROM maddress 
  WHERE ST_DWithin(geocode, 'POINT(20.0924758 72.7341809 19.137381,72.837223)' , 100.0);

i wish to select the addresses between the points mentioned in query.

I have checked syntax and as per syntax i have put values in query.Please let me know the correction.

Answer 1

I hade the same error when using:

select ST_GeomFromText('POINT(-27.75 ,114.75)', 3857)

instead of:

select ST_GeomFromText('POINT(-27.75 114.75)', 3857)

You need to remove the comma separator.

Answer 2

If you are trying to find points that are between two points, you probably need to use ST_DWithin twice, so that you get the points that are in the intersection of a circle based on point 1 and a circle based on point 2, eg,

SELECT addressid FROM maddress 
WHERE ST_DWithin(geocode, ST_MakePoint(20.0924758, 72.7341809), 100.0)
AND ST_DWithin(geocode, ST_MakePoint(19.137381, 72.837223), 100.0);

Note that the units are in SRID units, which in your case appears to be 4326, so you might want to convert your coordinates to meters, use a geography data type, so the distance will be in meters, or convert the distance to degrees -- there are various choices. I'm sure you have seen the ST_DWithin docs that explains these options. You might also want to consider the use_spheroid parameter.

Instead of ST_MakePoint above, you can also use ST_GeomFromText which takes the form 'POINT(x y)' as you are originally had an optionally allows you to specify the SRID, eg, using 4326, you can also write the query as:

SELECT addressid FROM maddress 
WHERE ST_DWithin(geocode, ST_GeomFromText('POINT(20.0924758 72.7341809)',4326), 100.0)
AND ST_DWithin(geocode, ST_GeomFromText('POINT(19.137381 72.837223)', 4326), 100.0);

EDIT Following comments from OP, it turns out that geocode is not a geometry column. You can fix this by running.

ALTER TABLE maddress ADD COLUMN geom GEOMETRY  (POINT, 4326);
UPDATE maddress set geom=ST_MakePoint(lng,lat);
CREATE INDEX ix_spatial_geom on maddress using gist(geom);

You will then need to use geom instead of geocode in the queries above. I am assuming that your points are in 4326.