14

PostgreSQL 9.6 using cube First install the cube extension CREATE EXTENSION cube; Now we will create some n-dimensional space with 100,000 points in 50 dimensions. In addition we'll add a GIST index. CREATE TEMP TABLE space_nd AS SELECT i, cube(array_agg(random()::float)) AS c FROM generate_series(1,1e5) AS i CROSS JOIN LATERAL generate_series(1,50)...


5

There might be some spatial methods that are useful to some degree, but your biggest problem is going to be the Z value: Z-coordinates are not used in any calculations made by the library and are not carried through any library calculations. This is by design. Don't think of SQL spatial objects as "true" 3D-geometric objects, think of them as map ...


4

Generally, you can always wrap a query as subquery if you don't want to output all rows: SELECT title FROM (SELECT ...) sub; But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented). For the case at hand, it must be mentioned that you are using the additional module ...


3

The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input: SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need FROM addresses a JOIN cafes c ON c.id = a.cafe_id , (SELECT lonlat FROM addresses WHERE ...


3

I'm going to go out on a bit of a limb here and guess what you are trying to achieve. I suspect that you want to find the nearest neighbour from within the DS1 table for all the rows in DS1. For a test set I created the following randomly filled table. -- Test table CREATE TABLE DS1 ( MSPID INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY, Location ...


2

Specifying the spatial index using the WITH (INDEX = []) option works: SELECT TOP 1 [Location].STDistance(@Location) FROM [DS1] WITH (INDEX = [...]) INNER JOIN ... ON ... WHERE [Location].STDistance(@Location) IS NOT NULL AND @CurrentMSPID = [MSPID] ORDER BY [Location].STDistance(@Location);


2

A reach SELECT TOP 1 DS2.[Location].STDistance(@Location) FROM [DS1] DS1 INNER JOIN [DS2] DS2 ON DS1.[MSPID] = @CurrentMSPID and DS2.[MSPID] = @CurrentMSPID WHERE DS2.[Location].STDistance(@Location) IS NOT NULL ORDER BY DS2.[Location].STDistance(@Location); Do you really need the join? SELECT TOP 1 DS2.[Location].STDistance(@Location) FROM [...


2

I suggest a LATERAL subquery, best with a LEFT [OUTER] JOIN to preserve all input rows (displaying a NULL value in case there should be no match at all): SELECT v.reference_ts, t.most_recent_x FROM ( VALUES (timestamptz '2018-03-13 11:41:47.167973+00') -- type cast in first row , ('2018-03-13 11:41:47.198564+00') , ('2018-03-13 11:41:47....


2

Consider performing dimension reduction first (eg. Principle Component Analysis). Then your are doing NN in small number of dimensions with higher performance. You can use Pl/R to perform PCA inside postgres if needed.


2

Many parentheses are just distracting noise. Several casts seem unnecessary. Most importantly, the CTE seems to do nothing useful, remove it and just keep the WHERE condition: SELECT p.sid, -- active list count means not expired count(*) FILTER (WHERE cl.expired_at IS NULL) AS list_count_active, -- 30d list count DOES include those ...


2

Trigram similarity and distance operators put more weight on leading matches (prefix) automatically and to a lesser extent on trailing matches (suffix), due to the way trigrams are extracted from strings. The manual: Each word is considered to have two spaces prefixed and one space suffixed when determining the set of trigrams contained in the string. ...


1

This would achieve it: SELECT m1.id1, m2.* FROM map1 m1 CROSS JOIN LATERAL ( SELECT ST_HausdorffDistance(m1.g1, m2.g2) AS h_dist, m2.id2, m2.g2 FROM map2 m2 WHERE ST_HausdorffDistance(m1.g1, m2.g2) < 2 ORDER BY 1, 2 LIMIT 2 ) m2; Returns 1 or 2 rows for every row in map1, extended with the top 2 corresponding row(s) in map2 (as ...


1

Got same error message, but without DB-Link. http://www.orafaq.com/forum/t/154844/ helped (replace c and s with your table aliases and spatial_index with your spatial index name): /*+ LEADING(c) USE_NL(c s) INDEX(s spatial_index)*/


1

With a SELF JOIN, in the form of an inner join if you know the distance. Or a cross if you don't. SELECT spatial_id, geom FROM rhp_tvp.spatial AS rt1 JOIN rhp_tvp.spatial AS rt2 USING ST_DWithin( rt1.geom, rt2.geom, MAX_DISTANCE ) WHERE rt1 ... -- SELECT POINT. ORDER BY rt1.geom <-> rt2.geom LIMIT 10 If you don't know the max distance of one point ...


1

A possible Solution to the Problem I found working is a step-by-step solution which looks like this: BEGIN; CREATE TEMP TABLE route ON COMMIT DROP AS SELECT seq, source, target, km, kmh, clazz, geom_way FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, (SELECT ST_Expand(ST_Extent(geom_vertex),0.1) as box FROM de_2po_vertex ...


Only top voted, non community-wiki answers of a minimum length are eligible