Your problem is that ST_MakePoint nad ST_MakeLine doesn't take WKT/EWKT format as input.
Working example query : ( My test table is in SRID 4326 )
SELECT ST_Distance(p.geom, ST_SetSRID(ST_MakePoint(1.0, 2.0),4326)) as distance,p.fips
FROM world p
WHERE ST_DWithin(p.geom, ST_SetSRID(ST_MakePoint(1.0, 2.0),4326),10)
ORDER BY ST_Distance(p.geom, ST_SetSRID(ST_MakePoint(1.0, 2.0),4326)) ASC LIMIT 5
From PostGIS manual (http://postgis.net/docs/index.html)
SELECT ST_AsEWKT(ST_MakeLine(ARRAY[ST_MakePoint(1,2,3),
ST_MakePoint(3,4,5), ST_MakePoint(6,6,6)]));
st_asewkt
So ST_MakeLine wants geoms to work. Easiest way to write your code will be replace ST_MakePoint with ST_GeomFromEWKT() OR ST_GEomFromWKT() (note that you need ST_SetSRID with that)
Example : SELECT ST_GeomFromEWKT('SRID=4269;POINT(-71.064544 42.28787)');
Following example replaces ST_Makeline with ST_GromFromEWKT. Note 900973 is old SRID for google mercator, you should use 3857. Example uses my test table world which has administrative boundaries of the world in srid 4326. That is the reason why there is ST_Transform(geom, SRID) call
SELECT ST_Distance(p.geom,
ST_Transform(ST_GeomFromEWKT('SRID=3857; POLYGON((2318122.615 4740270.8015625,2355043.3 4731221.6140625,2320294.42 4716380.9465625,2318122.615 4740270.8015625))'),4326)
) as distance,p.fips
FROM world p
WHERE ST_DWithin(p.geom,
ST_Transform(ST_GeomFromEWKT('SRID=3857; POLYGON((2318122.615 4740270.8015625,2355043.3 4731221.6140625,2320294.42 4716380.9465625,2318122.615 4740270.8015625))'),4326)
,10)
ORDER BY ST_Distance(p.geom,
ST_Transform(ST_GeomFromEWKT('SRID=3857; POLYGON((2318122.615 4740270.8015625,2355043.3 4731221.6140625,2320294.42 4716380.9465625,2318122.615 4740270.8015625))'),4326)
) ASC LIMIT 5
So change your "je" Variable to contain WKT (http://en.wikipedia.org/wiki/Well-known_text) text, add SRID=xxx to make it EWKT and pass it to ST_GeomFromEWKT() instead.
It looks like I just need to construct another array, like this (I also switched to using ST_Distance for ordering the first array):
SELECT l.gid, (SELECT ARRAY(SELECT c.city_name
FROM bndry.us_cities c
ORDER BY ST_Distance(l.geom. c.the_geom LIMIT 5)),
(SELECT ARRAY SELECT(ST_Distance(l.geom, c.the_geom)
FROM bndry.us_cities c
ORDER BY ST_Distance(l.geom, c.the_geom) limit 5))
FROM offshore_meta.load_centers l;
Best Answer
This is essentially a duplicate question of multiple others, with the sole difference being a table self-join.
However, all queries currently present in this post have delicate CRS misunderstandings, at least when it comes to distances:
ST_DWithin
; the units of that value are CRS dependent, thus, as the data is in EPSG:4236, you are searching in a radius of 10000 degrees!Arguably the best way to realize true KNN searches uses the
LATERAL JOIN
in conjunction with the<->
KNN operator, and, optionally but likely not required, a limiting radius filter (e.g.ST_DWithin
orST_Expand
+&&
BBox comparator).Concerning the units, one could choose a on-the-fly cast to geography type to tackle the CRS/distance issues and get the most precise distances in one go, using speroidal (or, quicker, spherical) algebra.
Runing
will return
dist
in meter to the nearest neighborp2.id
for eachp1.id
.Note: due to the cast to geography, units for any accepting function will be in meter as well, thus the
10000
again.As already mentioned, it is essential that you have a proper index in place! Checking the
EXPLAIN ANALYZE
is crucial to find out if it is actually used (although you can tell it is if you get results within your lifetime I guess...), and runningVACUUM ANALYZE <table_name>
in advance can help to enforce its use.Now, the liberal use of the on-the-fly cast to geography will take a heavy toll on execution speed. I´d recommend to either project the data to a suitable projection for distance measurements of your area, or, possibly better, change the geometry type to geography; both can be achieved by adding a new column, if you want your original
geom
s to stay untouched, and add an own index to it.Using test data on 70.000 points with porperly indexed geography column (having added a second one) took about 1 min. to complete the initial, uncached run on a mid tech setup.