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.
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.
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句