Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
Insert "EXPLAIN ANALYZE" to the beginning of your "select" query, and update your question. –  Mike T May 24 '12 at 23:01
    
Updated. It shows that the index is not used in the 2nd query. Hm... –  pajton May 25 '12 at 8:22
    
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 '12 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. –  Nicklas Avén May 25 '12 at 11:08
    
To clearify, there is an index option handling more than 2 dimensions, but I don't think <-> operator supports it. –  Nicklas Avén May 25 '12 at 11:52

1 Answer 1

<-> 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)'));
share|improve this answer
    
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 '12 at 14:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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