5

I am pretty new to PostGIS and I am trying to solve the following problem.

I need a table with 3D points (x,y,z) that will contain around 1-10million points. I want to be able to efficiently ask the table for 100-1000 points that are closest to some given point - parameter that is going to be different for each query.

Now, I started playling around this with following table:

CREATE TABLE aa ( id serial NOT NULL, point point, s integer ) WITH ( OIDS=FALSE );

CREATE INDEX aa_p_s_idx ON aa USING gist (point );

Queries like this:

select * from aa order by point <-> '0,0' limit 100;

are lightning fast (between 10-100msec).

But the problems started when I wanted to move to 3D. It seems I cannot use the point type anymore - I had to use geometry. And the <-> operator works on 2D distance. I ended up with:

CREATE TABLE db_testpoints ( id serial NOT NULL, point geometry NOT NULL, CONSTRAINT db_testpoints_pkey PRIMARY KEY (id ),
CONSTRAINT enforce_dims_point CHECK (st_ndims(point) = 3),
CONSTRAINT enforce_geotype_point CHECK (geometrytype(point) = 'POINT'::text OR point IS NULL), CONSTRAINT enforce_srid_point CHECK (st_srid(point) = 4326) ) WITH ( OIDS=FALSE );

CREATE INDEX db_testpoints_point_id ON db_testpoints USING gist
(point );

and even queries:

select * from db_testpoints order by point <-> ST_geomfromewkt('POINT(0 0 0)') limit 100;

take around 2000msec!

  1. Is there a possibility to have better performance here if I used some other column type, some other index etc?
  2. Is there a similar operator to <-> that works on 3D? I could probably use some distance function, but I heard then the index would not be used then...

Any pointers appreciated, thanks in advance!

Update

EXPLAIN ANALYZE for both queries shed some light:

"1. Limit  (cost=0.00..1.28 rows=10 width=24)"
"  ->  Index Scan using aa_p_s_idx on aa  (cost=0.00..985278.95 rows=7691709 width=24)"
"        Order By: (point <-> '(0,0)'::point)"

"2. Limit  (cost=46786.64..46786.66 rows=10 width=136)"
"  ->  Sort  (cost=46786.64..49389.14 rows=1041000 width=136)"
"        Sort Key: (((point)::box <-> '(0,0),(0,0)'::box))"
"        ->  Seq Scan on db_testpoints  (cost=0.00..24291.00 rows=1041000 width=136)"

So it looks like in the second query, the index is not used at all...how can I use it and is it going to work with 3D?

9
  • Insert "EXPLAIN ANALYZE" to the beginning of your "select" query, and update your question.
    – Mike T
    May 24, 2012 at 23:01
  • Updated. It shows that the index is not used in the 2nd query. Hm...
    – pajton
    May 25, 2012 at 8:22
  • 1
    I figured so, but I'm not sure why as I can't reproduce it. For me, with PostGIS 2.0 your second query is using the index and takes ~40 ms on 1M rows of PointZ data (fast).
    – Mike T
    May 25, 2012 at 9:40
  • Do you want to use the 3d distance or just do the same things with points having a z-value? I don't think you can get the index to work on real 3d distances, but it should be possible to get it ordered by 2d-distances even if the points has z-values. May 25, 2012 at 11:08
  • To clearify, there is an index option handling more than 2 dimensions, but I don't think <-> operator supports it. May 25, 2012 at 11:52

1 Answer 1

3

<-> is not supported for 3D indexes (though it looks like you have a 2-d index so it should be doing a 2D check).

Anyway use ST_3DDwithin to use an index

SELECT * FROM (select * from db_testpoints WHERE ST_3dDwithin(point,ST_geomfromewkt('POINT(0 0 0)'),somevalue_that_will_guarantee_100_records) ) As foo 
ORDER BY ST_3DDistance(point,ST_geomfromewkt('POINT(0 0 0)'));
1
  • 1
    Yeah, I thought on using ST_3DDwithin(), but the hard part is in knowing the distance value:). You mentioned 3D index. Am I using 2D index? How can I created the 3D index then and what is the difference?
    – pajton
    May 31, 2012 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.