[postgis-users] ST_DWithin is not using SpatialIndex with Subquery
Hi folks
first of here some system informations:
Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW Raid
PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
POSTGIS=2.0.0 r9605 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.7.1, 23
September 2009 GDAL=GDAL 1.9.1, released 2012/05/15 LIBXML=2.7.8
LIBJSON=UNKNOWN TOPOLOGY RASTER
Database:
TableName: PointTbl_v1
Index: idx_pdb_v1
NumRows:4.09806e+08
TableSize: 280 GB
IndexSize: 21 GB[Gist on(point_pos)]
GeometryCo:point_pos
Type:POINT
Dimensions: 2
SRID: 4326
/---
TableName:PointTbl_v5
Index: idx_pdb_v5
NumRows: 4.16218e+07
TableSize: 19 GB
IndexSize: 2344 MB [Gist on(point_pos)]
Primarykey:false
GeometryCo: point_pos
Type: POINT
Dimensions:2
SRID: 4326
/---
TableName:NetTbl
Index:idx_net
NumRows: 270615
TableSize: 195 MB
IndexSize: 17 MB
Primarykey: NetTbl_pk
GeometryCo:net_geom
Type:LINESTRING
Dimensions: 2
SRID: 4326
Basically I'm trying to gather all points from PointTbl_v5 / PointTbl_v1
that are close or on a LineString in NetTbl;
Here ist the Query I'm trying to run:
SELECT ST_AsBinary(point_pos) AS point_pos, oid, ..., type
FROM PointTbl_v5
WHERE ST_DWithin(point_pos,(SELECT net_geom from NetTbl where ogc_fid =
500) ,5e-05);
This query takes 319005 msec to return which is very slow due to the a seq
scan done on PoinDB_v5
EXPLAIN ANALYSE
Seq Scan on PointTbl_v5 (cost=108.36..10013364820.01
rows=13873927 width=202) (actual time=199926.978..318895.494 rows=5
loops=1)
Filter: st_dwithin(point_pos, $0, 5e-05::double precision)
InitPlan 1 (returns $0)
- Index Scan using NetTbl_pk on NetTbl (cost=0.00..8.36 rows=1
width=847) (actual time=2.069..2.075 rows=1 loops=1)
Index Cond: (ogc_fid = 2)
Total runtime: 318895.583 ms
The query:
SELECT ST_AsText(net_geom) from NetTbl where ogc_fid = 2
returns in 16 ms;
If I instert the Geometry by Hand into the first query like this:
SELECT ST_AsBinary(point_pos) AS point_pos, oid, country, federalstate,
district, town
FROM PointTbl_v5
WHERE ST_DWithin(point_pos, ST_GeomFromText(
'LINESTRING()',4326),5e-05);
This query return in 63ms on the small table and 766ms on the bigger table.
EXPLAIN ANALYSE
Index Scan using idx_pdb_v5 on PointTbl_v5 (cost=0.00..147.61 rows=1
width=202) (actual time=0.047..1.050 rows=23 loops=1)
Index Cond: (point_pos '0103.A40'::geometry)
Filter: (('0102.4A40'::geometry st_expand(point_pos,
5e-05::double precision)) AND _st_dwithin(point_pos,
'01020.A40'::geometry, 5e-05::double precision))
Total runtime: 1.080 ms
Does anybody have a clue why the first query with the subquery for the
LineString Geometry withing ST_DWithin not using the Spatial index?
seqscan_enabled is turned off...
cheers Thomas
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin is not using SpatialIndex with Subquery
I think your query does not use the index on net_geom because it is not a
spatial index (not a gist one) on the geometry, but a btree index on the id.
You should make the index (create index net_geom_gist on net_geom using
gist(geom) ) and try again your query
Hugues.
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de Thomas
Klemmer
Envoyé : mardi 11 septembre 2012 11:09
À : postgis-users@postgis.refractions.net
Objet : [postgis-users] ST_DWithin is not using SpatialIndex with Subquery
Hi folks
first of here some system informations:
Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW Raid
PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.6.3-1ubuntu5) 4.6.3, 64-bit
POSTGIS=2.0.0 r9605 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.7.1, 23 September
2009 GDAL=GDAL 1.9.1, released 2012/05/15 LIBXML=2.7.8 LIBJSON=UNKNOWN
TOPOLOGY RASTER
Database:
TableName: PointTbl_v1
Index: idx_pdb_v1
NumRows:4.09806e+08
TableSize: 280 GB
IndexSize: 21 GB[Gist on(point_pos)]
GeometryCo:point_pos
Type:POINT
Dimensions: 2
SRID: 4326
/---
TableName:PointTbl_v5
Index: idx_pdb_v5
NumRows: 4.16218e+07
TableSize: 19 GB
IndexSize: 2344 MB [Gist on(point_pos)]
Primarykey:false
GeometryCo: point_pos
Type: POINT
Dimensions:2
SRID: 4326
/---
TableName:NetTbl
Index:idx_net
NumRows: 270615
TableSize: 195 MB
IndexSize: 17 MB
Primarykey: NetTbl_pk
GeometryCo:net_geom
Type:LINESTRING
Dimensions: 2
SRID: 4326
Basically I'm trying to gather all points from PointTbl_v5 / PointTbl_v1 that
are close or on a LineString in NetTbl;
Here ist the Query I'm trying to run:
SELECT ST_AsBinary(point_pos) AS point_pos, oid, ..., type
FROM PointTbl_v5
WHERE ST_DWithin(point_pos,(SELECT net_geom from NetTbl where ogc_fid = 500)
,5e-05);
This query takes 319005 msec to return which is very slow due to the a seq scan
done on PoinDB_v5
EXPLAIN ANALYSE
Seq Scan on PointTbl_v5 (cost=108.36..10013364820.01 rows=13873927
width=202) (actual time=199926.978..318895.494 rows=5 loops=1)
Filter: st_dwithin(point_pos, $0, 5e-05::double precision)
InitPlan 1 (returns $0)
- Index Scan using NetTbl_pk on NetTbl (cost=0.00..8.36 rows=1
width=847) (actual time=2.069..2.075 rows=1 loops=1)
Index Cond: (ogc_fid = 2)
Total runtime: 318895.583 ms
The query:
SELECT ST_AsText(net_geom) from NetTbl where ogc_fid = 2
returns in 16 ms;
If I instert the Geometry by Hand into the first query like this:
SELECT ST_AsBinary(point_pos) AS point_pos, oid, country, federalstate,
district, town
FROM PointTbl_v5
WHERE ST_DWithin(point_pos, ST_GeomFromText( 'LINESTRING()',4326),5e-05);
This query return in 63ms on the small table and 766ms on the bigger table.
EXPLAIN ANALYSE
Index Scan using idx_pdb_v5 on PointTbl_v5 (cost=0.00..147.61 rows=1
width=202) (actual time=0.047..1.050 rows=23 loops=1)
Index Cond: (point_pos '0103.A40'::geometry)
Filter: (('0102.4A40'::geometry st_expand(point_pos, 5e-05::double
precision)) AND _st_dwithin(point_pos, '01020.A40'::geometry, 5e-05::double
precision))
Total runtime: 1.080 ms
Does anybody have a clue why the first query with the subquery for the
LineString Geometry withing ST_DWithin not using the Spatial index?
seqscan_enabled is turned off...
cheers Thomas
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin is not using SpatialIndex with Subquery
Hi hugues,
net_geom has a spatial index, the postam is just using the primary key on
this tabel since I am not useing any spatial filter to get the LINESTING
out of the table (just where ogs_fid = 2 which is the primary key).
The cruzial part is the seq scen on the large point table which should not
be used since the ST_DWithin is a spatial filter thus the spatial index
should be used.
TK
2012/9/11 Francois Hugues hugues.franc...@irstea.fr
**
I think your query does not use the index on net_geom because it is not a
spatial index (not a gist one) on the geometry, but a btree index on the id.
You should make the index (create index net_geom_gist on net_geom using
gist(geom) ) and try again your query
Hugues.
--
*De :* postgis-users-boun...@postgis.refractions.net [mailto:
postgis-users-boun...@postgis.refractions.net] *De la part de* Thomas
Klemmer
*Envoyé :* mardi 11 septembre 2012 11:09
*À :* postgis-users@postgis.refractions.net
*Objet :* [postgis-users] ST_DWithin is not using SpatialIndex with
Subquery
Hi folks
first of here some system informations:
Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW Raid
PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
POSTGIS=2.0.0 r9605 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.7.1, 23
September 2009 GDAL=GDAL 1.9.1, released 2012/05/15 LIBXML=2.7.8
LIBJSON=UNKNOWN TOPOLOGY RASTER
Database:
TableName: PointTbl_v1
Index: idx_pdb_v1
NumRows:4.09806e+08
TableSize: 280 GB
IndexSize: 21 GB[Gist on(point_pos)]
GeometryCo:point_pos
Type:POINT
Dimensions: 2
SRID: 4326
/---
TableName:PointTbl_v5
Index: idx_pdb_v5
NumRows: 4.16218e+07
TableSize: 19 GB
IndexSize: 2344 MB [Gist on(point_pos)]
Primarykey:false
GeometryCo: point_pos
Type: POINT
Dimensions:2
SRID: 4326
/---
TableName:NetTbl
Index:idx_net
NumRows: 270615
TableSize: 195 MB
IndexSize: 17 MB
Primarykey: NetTbl_pk
GeometryCo:net_geom
Type:LINESTRING
Dimensions: 2
SRID: 4326
Basically I'm trying to gather all points from PointTbl_v5 / PointTbl_v1
that are close or on a LineString in NetTbl;
Here ist the Query I'm trying to run:
SELECT ST_AsBinary(point_pos) AS point_pos, oid, ..., type
FROM PointTbl_v5
WHERE ST_DWithin(point_pos,(SELECT net_geom from NetTbl where ogc_fid =
500) ,5e-05);
This query takes 319005 msec to return which is very slow due to the a seq
scan done on PoinDB_v5
EXPLAIN ANALYSE
Seq Scan on PointTbl_v5 (cost=108.36..10013364820.01
rows=13873927 width=202) (actual time=199926.978..318895.494 rows=5
loops=1)
Filter: st_dwithin(point_pos, $0, 5e-05::double precision)
InitPlan 1 (returns $0)
- Index Scan using NetTbl_pk on NetTbl (cost=0.00..8.36 rows=1
width=847) (actual time=2.069..2.075 rows=1 loops=1)
Index Cond: (ogc_fid = 2)
Total runtime: 318895.583 ms
The query:
SELECT ST_AsText(net_geom) from NetTbl where ogc_fid = 2
returns in 16 ms;
If I instert the Geometry by Hand into the first query like this:
SELECT ST_AsBinary(point_pos) AS point_pos, oid, country, federalstate,
district, town
FROM PointTbl_v5
WHERE ST_DWithin(point_pos, ST_GeomFromText(
'LINESTRING()',4326),5e-05);
This query return in 63ms on the small table and 766ms on the bigger table.
EXPLAIN ANALYSE
Index Scan using idx_pdb_v5 on PointTbl_v5 (cost=0.00..147.61 rows=1
width=202) (actual time=0.047..1.050 rows=23 loops=1)
Index Cond: (point_pos '0103.A40'::geometry)
Filter: (('0102.4A40'::geometry st_expand(point_pos,
5e-05::double precision)) AND _st_dwithin(point_pos,
'01020.A40'::geometry, 5e-05::double precision))
Total runtime: 1.080 ms
Does anybody have a clue why the first query with the subquery for the
LineString Geometry withing ST_DWithin not using the Spatial index?
seqscan_enabled is turned off...
cheers Thomas
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_DWithin-- unexpected behavior?
Hi All, Running 1.3.5 still :). Found some unexpected (to me), but pleasant behavior with ST_DWithin. What we're doing is creating a function that we can use to randomize a point within a minimum and maximum bounds. Our original approach was to generate the geometry of the difference of two buffers (a donut, if you will) and then place a random point within that. Better to test with ST_DWithin directly then buffer, so we rewrote: BEGIN xmin = ST_XMin(geom) - dough; deltax = ST_XMax(geom) - xmin + dough; ymin = ST_YMin(geom) - dough; deltay = ST_YMax(geom) - ymin + dough; WHILE i itermax LOOP i = i + 1; pointx = xmin + deltax * random(); pointy = ymin + deltay * random(); returnpoint = ST_SetSRID( ST_MakePoint( pointx, pointy ), ST_SRID(geom) ); EXIT WHEN ST_DWithin( returnpoint, geom, dough ) AND NOT ST_DWithin( returnpoint, geom, nut ); END LOOP; IF i itermax THEN RAISE NOTICE 'Reached maximum iterations without placing point inside donut.'; END IF; RETURN returnpoint; END; The puzzling part is that even if the parameters are passed out of order, i.e. the inner ring is passed for the outer ring and vice versa, we get a fine result. It's a nice unexpected behavior, but unexpected none-the-less. Anyone have any thoughts on why this works? Full function below: http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather Geographic Information Systems (GIS) Manager (216) 635-3243 s...@clevelandmetroparks.com http://www.clemetparks.com/ clevelandmetroparks.com CREATE OR REPLACE FUNCTION ST_Donut ( geom Geometry, dough DOUBLE PRECISION, nut DOUBLE PRECISION, itermax INTEGER) RETURNS Geometry AS $$ DECLARE xmin DOUBLE PRECISION; ymin DOUBLE PRECISION; deltax DOUBLE PRECISION; deltay DOUBLE PRECISION; pointx DOUBLE PRECISION; pointy DOUBLE PRECISION; returnpoint Geometry; i INTEGER := 0; nut1 DOUBLE PRECISION; dough1 DOUBLE PRECISION; BEGIN xmin = ST_XMin(geom) - dough; deltax = ST_XMax(geom) - xmin + dough; ymin = ST_YMin(geom) - dough; deltay = ST_YMax(geom) - ymin + dough; WHILE i itermax LOOP i = i + 1; pointx = xmin + deltax * random(); pointy = ymin + deltay * random(); returnpoint = ST_SetSRID( ST_MakePoint( pointx, pointy ), ST_SRID(geom) ); EXIT WHEN ST_DWithin( returnpoint, geom, dough ) AND NOT ST_DWithin( returnpoint, geom, nut ); END LOOP; IF i itermax THEN RAISE NOTICE 'Reached maximum iterations without placing point inside donut.'; END IF; RETURN returnpoint; END; $$ LANGUAGE plpgsql; image001.png___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin-- unexpected behavior?
Hi, Sorry for the noise, but the function probably should be: BEGIN xmin = ST_X(geom) - dough; -- establish lower left for buffer ymin = ST_Y(geom) - dough; -- delta = dough * 2; -- WHILE i itermax LOOP i = i + 1; pointx = xmin + delta * random(); -- generate random point value pointy = ymin + delta * random(); returnpoint = ST_SetSRID( ST_MakePoint( pointx, pointy ), ST_SRID(geom) ); EXIT WHEN ST_DWithin( returnpoint, geom, dough ) -- test to see if inside outer ring AND NOT ST_DWithin( returnpoint, geom, nut ); --test to see if inside inner ring END LOOP; IF i itermax THEN RAISE NOTICE 'Reached maximum iterations without placing point inside donut.'; END IF; RETURN returnpoint; END; Even so, the question remains :). Steve http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather Geographic Information Systems (GIS) Manager (216) 635-3243 s...@clevelandmetroparks.com http://www.clemetparks.com/ clevelandmetroparks.com From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen V. Mather Sent: Tuesday, October 25, 2011 11:04 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] ST_DWithin-- unexpected behavior? Hi All, Running 1.3.5 still :). Found some unexpected (to me), but pleasant behavior with ST_DWithin. What we're doing is creating a function that we can use to randomize a point within a minimum and maximum bounds. Our original approach was to generate the geometry of the difference of two buffers (a donut, if you will) and then place a random point within that. Better to test with ST_DWithin directly then buffer, so we rewrote: BEGIN xmin = ST_XMin(geom) - dough; deltax = ST_XMax(geom) - xmin + dough; ymin = ST_YMin(geom) - dough; deltay = ST_YMax(geom) - ymin + dough; WHILE i itermax LOOP i = i + 1; pointx = xmin + deltax * random(); pointy = ymin + deltay * random(); returnpoint = ST_SetSRID( ST_MakePoint( pointx, pointy ), ST_SRID(geom) ); EXIT WHEN ST_DWithin( returnpoint, geom, dough ) AND NOT ST_DWithin( returnpoint, geom, nut ); END LOOP; IF i itermax THEN RAISE NOTICE 'Reached maximum iterations without placing point inside donut.'; END IF; RETURN returnpoint; END; The puzzling part is that even if the parameters are passed out of order, i.e. the inner ring is passed for the outer ring and vice versa, we get a fine result. It's a nice unexpected behavior, but unexpected none-the-less. Anyone have any thoughts on why this works? Full function below: http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather Geographic Information Systems (GIS) Manager (216) 635-3243 s...@clevelandmetroparks.com http://www.clemetparks.com/ clevelandmetroparks.com CREATE OR REPLACE FUNCTION ST_Donut ( geom Geometry, dough DOUBLE PRECISION, nut DOUBLE PRECISION, itermax INTEGER) RETURNS Geometry AS $$ DECLARE xmin DOUBLE PRECISION; ymin DOUBLE PRECISION; deltax DOUBLE PRECISION; deltay DOUBLE PRECISION; pointx DOUBLE PRECISION; pointy DOUBLE PRECISION; returnpoint Geometry; i INTEGER := 0; nut1 DOUBLE PRECISION; dough1 DOUBLE PRECISION; BEGIN xmin = ST_XMin(geom) - dough; deltax = ST_XMax(geom) - xmin + dough; ymin = ST_YMin(geom) - dough; deltay = ST_YMax(geom) - ymin + dough; WHILE i itermax LOOP i = i + 1; pointx = xmin + deltax * random(); pointy = ymin + deltay * random
Re: [postgis-users] ST_DWITHIN indices
Thanks. I started using GIST.
It works.
Thanks,
Sairam Krishnamurthy
+1 612 859 8161
On Thu, May 5, 2011 at 8:39 PM, Ben Madin li...@remoteinformation.com.auwrote:
Sairam,
On 06/05/2011, at 5:50 AM, Sairam Krishnamurthy wrote:
Table structure: lat AS double, lon AS double, spatialPoint AS point.
I have a query that uses ST_DWITHIN. I was under the impression that this
function will use the gist index on spatialPoint.
Index query:
CREATE INDEX table_spatial_index ON table USING btree
(spatialPoint);
St_DWithin does use a bounding box if there are suitable indexes. I'm only
using 1.5, so things might be different if you are using 2.0 or 1.3, but I
think if you want to use a GIST index you need to create a GIST index, not a
b-tree. I thought - and I stress I'm not an expert on indexes that b-tree
was one-dimensional.
Select query:
SELECT lat, lon FROM table WHERE ST_DWITHIN(table.spatialPoint,
ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);
Also, I'd caution against using ST_DWithin and non-projected data systems.
you won't actually be seeing a circle, even if you do appear to be close to
the equator.
cheers
Ben
Query plan:
EXPLAIN SELECT lat, lon FROM EVI250m WHERE
ST_DWITHIN(EVI250m.spatialPoint,
ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);
QUERY PLAN
-
-
Seq Scan on EVI250m (cost=0.00..4757082.00 rows=1 width=16)
Filter: ((spatialPoint
'010320E610010005A0224DB03F00C05D6B324000A0224DB03F0020EE6B324000C050DDB03F0020EE6
) AND _st_dwithin(spatialPoint,
'010120E6102A7288B83995B03FCFF753E3A56B3240'::geometry,
0.0011::double precision) AND ('010120E6102A7288B83
recision)))
(2 rows)
Any thoughts ?
Thanks,
Sairam Krishnamurthy
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_DWITHIN indices
All,
Table structure: lat AS double, lon AS double, spatialPoint AS point.
I have a query that uses ST_DWITHIN. I was under the impression that this
function will use the gist index on spatialPoint.
Index query:
CREATE INDEX table_spatial_index ON table USING btree (spatialPoint);
Select query:
SELECT lat, lon FROM table WHERE ST_DWITHIN(table.spatialPoint,
ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);
Query plan:
EXPLAIN SELECT lat, lon FROM EVI250m WHERE
ST_DWITHIN(EVI250m.spatialPoint,
ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);
QUERY PLAN
-
-
Seq Scan on EVI250m (cost=0.00..4757082.00 rows=1 width=16)
Filter: ((spatialPoint
'010320E610010005A0224DB03F00C05D6B324000A0224DB03F0020EE6B324000C050DDB03F0020EE6
) AND _st_dwithin(spatialPoint,
'010120E6102A7288B83995B03FCFF753E3A56B3240'::geometry,
0.0011::double precision) AND ('010120E6102A7288B83
recision)))
(2 rows)
Any thoughts ?
Thanks,
Sairam Krishnamurthy
+1 612 859 8161
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWITHIN indices
Sairam,
On 06/05/2011, at 5:50 AM, Sairam Krishnamurthy wrote:
Table structure: lat AS double, lon AS double, spatialPoint AS point.
I have a query that uses ST_DWITHIN. I was under the impression that this
function will use the gist index on spatialPoint.
Index query:
CREATE INDEX table_spatial_index ON table USING btree (spatialPoint);
St_DWithin does use a bounding box if there are suitable indexes. I'm only
using 1.5, so things might be different if you are using 2.0 or 1.3, but I
think if you want to use a GIST index you need to create a GIST index, not a
b-tree. I thought - and I stress I'm not an expert on indexes that b-tree was
one-dimensional.
Select query:
SELECT lat, lon FROM table WHERE ST_DWITHIN(table.spatialPoint,
ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);
Also, I'd caution against using ST_DWithin and non-projected data systems. you
won't actually be seeing a circle, even if you do appear to be close to the
equator.
cheers
Ben
Query plan:
EXPLAIN SELECT lat, lon FROM EVI250m WHERE
ST_DWITHIN(EVI250m.spatialPoint,
ST_SetSRID(ST_MakePoint(0.064777,18.420500), 4326), 0.0011);
QUERY PLAN
-
-
Seq Scan on EVI250m (cost=0.00..4757082.00 rows=1 width=16)
Filter: ((spatialPoint
'010320E610010005A0224DB03F00C05D6B324000A0224DB03F0020EE6B324000C050DDB03F0020EE6
) AND _st_dwithin(spatialPoint,
'010120E6102A7288B83995B03FCFF753E3A56B3240'::geometry,
0.0011::double precision) AND ('010120E6102A7288B83
recision)))
(2 rows)
Any thoughts ?
Thanks,
Sairam Krishnamurthy
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_DWithin on 2 columns which are the same
Hi, I have been trying, with no luck, to implement a query to return me all the pairs of rows that are within a certain range of each other. I searched the forum before trying to find a solution but I haven't been able to. Sorry if there is a solution to this problem already that I didn't see. Suppose you have a 2 column table cars with an integer ID, and a geography Location representing the position of the car globally. I want to write a query that will return me pairs of IDs of cars that are within say 1km of each other. Having looked at the functions available ST_DWithin seems the obvious choice but I haven't been able to actually use it for what I want. I've started out by simply testing if it matches each car as being in range with itself : SELECT * FROM cars WHERE ST_DWithin(location, location, 1); This returned all the entries from the cars table as expected (given that no cars where actually within a meter of each other). I then tried to find all the cars that are within a km of each other but don't have the same ID (to avoid matching a car with itself). Because of this extra constraint I need to somehow treat the location columns individually so I've tried the following: SELECT c1.id, c2.id FROM cars AS c1, cars AS c2 WHERE ST_DWithin(c1.location, c2.location, 1000) AND c1.id != c2.id; But this query never actually finishes computing (the number of entries in my cars table is around 30k and after 2 hours of the query being executed I still didn't get back a result). I would greatly appreciate any help in computing this query as well as any tips on performance. I should also probably mention that I did an indexing on the location column as suggested in the documentation: CREATE INDEX cars_gix ON cars USING GIST (location); Thank you in advance, Robert -- View this message in context: http://old.nabble.com/ST_DWithin-on-2-columns-which-are-the-same-tp31038958p31038958.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin on 2 columns which are the same
Robert, On 01/03/2011, at 4:47 PM, robertvc wrote: I have been trying, with no luck, to implement a query to return me all the pairs of rows that are within a certain range of each other. I searched the forum before trying to find a solution but I haven't been able to. Sorry if there is a solution to this problem already that I didn't see. I think you need to search on nearest neighbour. Suppose you have a 2 column table cars with an integer ID, and a geography Location representing the position of the car globally. I want to write a query that will return me pairs of IDs of cars that are within say 1km of each other. Having looked at the functions available ST_DWithin seems the obvious choice but I haven't been able to actually use it for what I want. I've started out by simply testing if it matches each car as being in range with itself : SELECT * FROM cars WHERE ST_DWithin(location, location, 1); This returned all the entries from the cars table as expected (given that no cars where actually within a meter of each other). This doesn't sound like my interpretation - it returned all the entries because each car's location was within 1 metre of itself (unsurprisingly). I then tried to find all the cars that are within a km of each other but don't have the same ID (to avoid matching a car with itself). Because of this extra constraint I need to somehow treat the location columns individually so I've tried the following: SELECT c1.id, c2.id FROM cars AS c1, cars AS c2 WHERE ST_DWithin(c1.location, c2.location, 1000) AND c1.id != c2.id; But this query never actually finishes computing (the number of entries in my cars table is around 30k and after 2 hours of the query being executed I still didn't get back a result). I would greatly appreciate any help in computing this query as well as any tips on performance. I should also probably mention that I did an indexing on the location column as suggested in the documentation: CREATE INDEX cars_gix ON cars USING GIST (location); Did you also VACUUM ANALYZE after creating the index? Performance-wise, using geometry instead of geography might help. You could try EXPLAIN to see where the slow point in the query is. How widespread are the cars - are you talking about 30 000 cars that might all be within 1 km of each other? Given that the direction of the relation doesn't matter maybe use an outer join something like SELECT c1.id, c2.id FROM cars c1 LEFT OUTER JOIN cars c2 ON ST_DWithin(c1.location, c2.location, 1000) AND c2 IS NOT NULL AND c1.id c2.id; (I have no idea if that would work!) cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin Performance
Hi Brent,
This is a little out there, but what about swapping out the created_on
timestamp type with a floating point type using epoch time (ie.,
extract(epoch FROM now())) and creating a gist index on both the location
(geography type) and created_on (now floating point) columns?
Carl
On Sat, Feb 12, 2011 at 6:24 PM, pcr...@pcreso.com wrote:
Hi Carl,
If most of your data is reasonably static (which time series data often
isn't) we have used this approach successfully with some 250,000,000 records
in a table:
use Postgres inheritance facility to partition the table in chunks by time,
eg: one year's data per partition. If your request is for data from one
year, it can pretty much ignore all the other partitions when searching for
data, which speeds things up.
Apply a Postgres clustered index to the timestamp column for each
partition.This physically orders the data on disk by the column values, and
given timeseries databases are often queried by time, this can significantly
improve performance, as each disk read will normally grab sequential data
needed by the query, reducing disk reads seek times required. A clustered
index does not need to be applied to the current year's partition unless
really useful, as maintaining it requires the index to be rebuilt after
every insert or update.
An order by/limit query can be significantly faster with data managed in
this way.
HTH,
Brent Wood
--- On *Sun, 2/13/11, Paul Ramsey pram...@opengeo.org* wrote:
From: Paul Ramsey pram...@opengeo.org
Subject: Re: [postgis-users] ST_DWithin Performance
To: PostGIS Users Discussion postgis-users@postgis.refractions.net
Date: Sunday, February 13, 2011, 11:42 AM
How many records get returned when you drop the LIMIT? If you have to
sort 700K things into order, I can see that taking a second or two.
P.
On Sat, Feb 12, 2011 at 2:16 PM, Carl S. Yestrau Jr.
c...@featureblend.com http://mc/compose?to=c...@featureblend.com
wrote:
When I removed the ORDER BY created_on DESC the results were almost
instantaneous (which makes sense as it just returns the first 'x'
result defined by LIMIT). I don't have access to the machine at the
moment, but can get the plan for that if it's helpful.
I'm pretty confused why things are so slow. The only thing I can think
of is possibly the data density, I have over 700K records within a 20
minute window.
On Sat, Feb 12, 2011 at 12:27 AM, strk
s...@keybit.nethttp://mc/compose?to=s...@keybit.net
wrote:
On Fri, Feb 11, 2011 at 02:27:16PM -0800, Carl S. Yestrau Jr. wrote:
Table schema:
Table public.geobits
Column |Type |
Modifiers
---+-+--
id| integer | not null default
nextval('geobits_id_seq'::regclass)
uuid | uuid| not null
raw | text| not null
search_config | regconfig |
ip| inet| not null
user_agent| text|
created_on| timestamp without time zone | default now()
location | geography(Point,4326) | not null
parent_id | integer |
language_code | text|
group_id | integer |
Indexes:
geobits_pkey PRIMARY KEY, btree (id)
geobits_uuid_key UNIQUE, btree (uuid)
geobits_created_on_index btree (created_on)
geobits_group_id_index btree (group_id)
geobits_location_index gist (location)
geobits_parent_id_index btree (parent_id)
geobits_search_config_index gin (to_tsvector(search_config, raw))
Foreign-key constraints:
geobits_group_id_fkey FOREIGN KEY (group_id) REFERENCES
groups(id)
geobits_language_code_fkey FOREIGN KEY (language_code)
REFERENCES languages(code) ON DELETE SET NULL
geobits_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
geobits(id) ON DELETE SET NULL
Referenced by:
TABLE geobits CONSTRAINT geobits_parent_id_fkey FOREIGN KEY
(parent_id) REFERENCES geobits(id) ON DELETE SET NULL
Slow Query:
EXPLAIN ANALYZE SELECT id, created_on FROM geobits WHERE
ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(180 90)'),
1000) ORDER BY created_on DESC LIMIT 10;
QUERY PLAN
--
Limit (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.057..4078.090 rows=10 loops=1)
- Sort (cost
Re: [postgis-users] ST_DWithin Performance
On Fri, Feb 11, 2011 at 02:27:16PM -0800, Carl S. Yestrau Jr. wrote:
Table schema:
Table public.geobits
Column |Type |
Modifiers
---+-+--
id| integer | not null default
nextval('geobits_id_seq'::regclass)
uuid | uuid| not null
raw | text| not null
search_config | regconfig |
ip| inet| not null
user_agent| text|
created_on| timestamp without time zone | default now()
location | geography(Point,4326) | not null
parent_id | integer |
language_code | text|
group_id | integer |
Indexes:
geobits_pkey PRIMARY KEY, btree (id)
geobits_uuid_key UNIQUE, btree (uuid)
geobits_created_on_index btree (created_on)
geobits_group_id_index btree (group_id)
geobits_location_index gist (location)
geobits_parent_id_index btree (parent_id)
geobits_search_config_index gin (to_tsvector(search_config, raw))
Foreign-key constraints:
geobits_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(id)
geobits_language_code_fkey FOREIGN KEY (language_code)
REFERENCES languages(code) ON DELETE SET NULL
geobits_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
geobits(id) ON DELETE SET NULL
Referenced by:
TABLE geobits CONSTRAINT geobits_parent_id_fkey FOREIGN KEY
(parent_id) REFERENCES geobits(id) ON DELETE SET NULL
Slow Query:
EXPLAIN ANALYZE SELECT id, created_on FROM geobits WHERE
ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(180 90)'),
1000) ORDER BY created_on DESC LIMIT 10;
QUERY PLAN
--
Limit (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.057..4078.090 rows=10 loops=1)
- Sort (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.053..4078.064 rows=10 loops=1)
Sort Key: created_on
Sort Method: top-N heapsort Memory: 17kB
- Seq Scan on geobits (cost=0.00..205352.08 rows=1
width=12) (actual time=0.015..3100.471 rows=708661 loops=1)
Filter: ((location
'010120E6100080664000805640'::geography) AND
('010120E6100080664000805640'::geography
_st_expand(location, 1000::double precision)) AND
_st_dwithin(location, '010120E61
00080664000805640'::geography, 1000::double precision, true))
Total runtime: 4078.127 ms
(7 rows)
The geobits_location_index gist (location) index is not being used,
for some reason. Why do you say ORDER BY is the culprit ? Does
the plan come out differently w/out that ?
--strk;
() Free GIS Flash consultant/developer
/\ http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin Performance
When I removed the ORDER BY created_on DESC the results were almost
instantaneous (which makes sense as it just returns the first 'x'
result defined by LIMIT). I don't have access to the machine at the
moment, but can get the plan for that if it's helpful.
I'm pretty confused why things are so slow. The only thing I can think
of is possibly the data density, I have over 700K records within a 20
minute window.
On Sat, Feb 12, 2011 at 12:27 AM, strk s...@keybit.net wrote:
On Fri, Feb 11, 2011 at 02:27:16PM -0800, Carl S. Yestrau Jr. wrote:
Table schema:
Table public.geobits
Column | Type |
Modifiers
---+-+--
id | integer | not null default
nextval('geobits_id_seq'::regclass)
uuid | uuid | not null
raw | text | not null
search_config | regconfig |
ip | inet | not null
user_agent | text |
created_on | timestamp without time zone | default now()
location | geography(Point,4326) | not null
parent_id | integer |
language_code | text |
group_id | integer |
Indexes:
geobits_pkey PRIMARY KEY, btree (id)
geobits_uuid_key UNIQUE, btree (uuid)
geobits_created_on_index btree (created_on)
geobits_group_id_index btree (group_id)
geobits_location_index gist (location)
geobits_parent_id_index btree (parent_id)
geobits_search_config_index gin (to_tsvector(search_config, raw))
Foreign-key constraints:
geobits_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(id)
geobits_language_code_fkey FOREIGN KEY (language_code)
REFERENCES languages(code) ON DELETE SET NULL
geobits_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
geobits(id) ON DELETE SET NULL
Referenced by:
TABLE geobits CONSTRAINT geobits_parent_id_fkey FOREIGN KEY
(parent_id) REFERENCES geobits(id) ON DELETE SET NULL
Slow Query:
EXPLAIN ANALYZE SELECT id, created_on FROM geobits WHERE
ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(180 90)'),
1000) ORDER BY created_on DESC LIMIT 10;
QUERY PLAN
--
Limit (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.057..4078.090 rows=10 loops=1)
- Sort (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.053..4078.064 rows=10 loops=1)
Sort Key: created_on
Sort Method: top-N heapsort Memory: 17kB
- Seq Scan on geobits (cost=0.00..205352.08 rows=1
width=12) (actual time=0.015..3100.471 rows=708661 loops=1)
Filter: ((location
'010120E6100080664000805640'::geography) AND
('010120E6100080664000805640'::geography
_st_expand(location, 1000::double precision)) AND
_st_dwithin(location, '010120E61
00080664000805640'::geography, 1000::double precision, true))
Total runtime: 4078.127 ms
(7 rows)
The geobits_location_index gist (location) index is not being used,
for some reason. Why do you say ORDER BY is the culprit ? Does
the plan come out differently w/out that ?
--strk;
() Free GIS Flash consultant/developer
/\ http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin Performance
How many records get returned when you drop the LIMIT? If you have to
sort 700K things into order, I can see that taking a second or two.
P.
On Sat, Feb 12, 2011 at 2:16 PM, Carl S. Yestrau Jr.
c...@featureblend.com wrote:
When I removed the ORDER BY created_on DESC the results were almost
instantaneous (which makes sense as it just returns the first 'x'
result defined by LIMIT). I don't have access to the machine at the
moment, but can get the plan for that if it's helpful.
I'm pretty confused why things are so slow. The only thing I can think
of is possibly the data density, I have over 700K records within a 20
minute window.
On Sat, Feb 12, 2011 at 12:27 AM, strk s...@keybit.net wrote:
On Fri, Feb 11, 2011 at 02:27:16PM -0800, Carl S. Yestrau Jr. wrote:
Table schema:
Table public.geobits
Column | Type |
Modifiers
---+-+--
id | integer | not null default
nextval('geobits_id_seq'::regclass)
uuid | uuid | not null
raw | text | not null
search_config | regconfig |
ip | inet | not null
user_agent | text |
created_on | timestamp without time zone | default now()
location | geography(Point,4326) | not null
parent_id | integer |
language_code | text |
group_id | integer |
Indexes:
geobits_pkey PRIMARY KEY, btree (id)
geobits_uuid_key UNIQUE, btree (uuid)
geobits_created_on_index btree (created_on)
geobits_group_id_index btree (group_id)
geobits_location_index gist (location)
geobits_parent_id_index btree (parent_id)
geobits_search_config_index gin (to_tsvector(search_config, raw))
Foreign-key constraints:
geobits_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(id)
geobits_language_code_fkey FOREIGN KEY (language_code)
REFERENCES languages(code) ON DELETE SET NULL
geobits_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
geobits(id) ON DELETE SET NULL
Referenced by:
TABLE geobits CONSTRAINT geobits_parent_id_fkey FOREIGN KEY
(parent_id) REFERENCES geobits(id) ON DELETE SET NULL
Slow Query:
EXPLAIN ANALYZE SELECT id, created_on FROM geobits WHERE
ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(180 90)'),
1000) ORDER BY created_on DESC LIMIT 10;
QUERY PLAN
--
Limit (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.057..4078.090 rows=10 loops=1)
- Sort (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.053..4078.064 rows=10 loops=1)
Sort Key: created_on
Sort Method: top-N heapsort Memory: 17kB
- Seq Scan on geobits (cost=0.00..205352.08 rows=1
width=12) (actual time=0.015..3100.471 rows=708661 loops=1)
Filter: ((location
'010120E6100080664000805640'::geography) AND
('010120E6100080664000805640'::geography
_st_expand(location, 1000::double precision)) AND
_st_dwithin(location, '010120E61
00080664000805640'::geography, 1000::double precision, true))
Total runtime: 4078.127 ms
(7 rows)
The geobits_location_index gist (location) index is not being used,
for some reason. Why do you say ORDER BY is the culprit ? Does
the plan come out differently w/out that ?
--strk;
() Free GIS Flash consultant/developer
/\ http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin Performance
Hi Paul,
~700K records get returned if I drop the LIMIT. Thanks for the feedback.
On Sat, Feb 12, 2011 at 2:42 PM, Paul Ramsey pram...@opengeo.org wrote:
How many records get returned when you drop the LIMIT? If you have to
sort 700K things into order, I can see that taking a second or two.
P.
On Sat, Feb 12, 2011 at 2:16 PM, Carl S. Yestrau Jr.
c...@featureblend.com wrote:
When I removed the ORDER BY created_on DESC the results were almost
instantaneous (which makes sense as it just returns the first 'x'
result defined by LIMIT). I don't have access to the machine at the
moment, but can get the plan for that if it's helpful.
I'm pretty confused why things are so slow. The only thing I can think
of is possibly the data density, I have over 700K records within a 20
minute window.
On Sat, Feb 12, 2011 at 12:27 AM, strk s...@keybit.net wrote:
On Fri, Feb 11, 2011 at 02:27:16PM -0800, Carl S. Yestrau Jr. wrote:
Table schema:
Table public.geobits
Column | Type |
Modifiers
---+-+--
id | integer | not null default
nextval('geobits_id_seq'::regclass)
uuid | uuid | not null
raw | text | not null
search_config | regconfig |
ip | inet | not null
user_agent | text |
created_on | timestamp without time zone | default now()
location | geography(Point,4326) | not null
parent_id | integer |
language_code | text |
group_id | integer |
Indexes:
geobits_pkey PRIMARY KEY, btree (id)
geobits_uuid_key UNIQUE, btree (uuid)
geobits_created_on_index btree (created_on)
geobits_group_id_index btree (group_id)
geobits_location_index gist (location)
geobits_parent_id_index btree (parent_id)
geobits_search_config_index gin (to_tsvector(search_config, raw))
Foreign-key constraints:
geobits_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(id)
geobits_language_code_fkey FOREIGN KEY (language_code)
REFERENCES languages(code) ON DELETE SET NULL
geobits_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
geobits(id) ON DELETE SET NULL
Referenced by:
TABLE geobits CONSTRAINT geobits_parent_id_fkey FOREIGN KEY
(parent_id) REFERENCES geobits(id) ON DELETE SET NULL
Slow Query:
EXPLAIN ANALYZE SELECT id, created_on FROM geobits WHERE
ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(180 90)'),
1000) ORDER BY created_on DESC LIMIT 10;
QUERY PLAN
--
Limit (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.057..4078.090 rows=10 loops=1)
- Sort (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.053..4078.064 rows=10 loops=1)
Sort Key: created_on
Sort Method: top-N heapsort Memory: 17kB
- Seq Scan on geobits (cost=0.00..205352.08 rows=1
width=12) (actual time=0.015..3100.471 rows=708661 loops=1)
Filter: ((location
'010120E6100080664000805640'::geography) AND
('010120E6100080664000805640'::geography
_st_expand(location, 1000::double precision)) AND
_st_dwithin(location, '010120E61
00080664000805640'::geography, 1000::double precision, true))
Total runtime: 4078.127 ms
(7 rows)
The geobits_location_index gist (location) index is not being used,
for some reason. Why do you say ORDER BY is the culprit ? Does
the plan come out differently w/out that ?
--strk;
() Free GIS Flash consultant/developer
/\ http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin Performance
On Thu, Feb 10, 2011 at 03:37:34PM -0800, Carl S. Yestrau Jr. wrote: I have a geography(Point,4326) column with a gist index with ~700K records. A simple query using the ST_DWithin() is taking about 15 secs. Any pointers from the resident experts? show explain analyze yourselect output ? --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin Performance
Thanks Mark,
Here's the additional information, the ORDER BY created_on DESC seems
to be the issue even though it's indexed.
Table schema:
Table public.geobits
Column |Type |
Modifiers
---+-+--
id| integer | not null default
nextval('geobits_id_seq'::regclass)
uuid | uuid| not null
raw | text| not null
search_config | regconfig |
ip| inet| not null
user_agent| text|
created_on| timestamp without time zone | default now()
location | geography(Point,4326) | not null
parent_id | integer |
language_code | text|
group_id | integer |
Indexes:
geobits_pkey PRIMARY KEY, btree (id)
geobits_uuid_key UNIQUE, btree (uuid)
geobits_created_on_index btree (created_on)
geobits_group_id_index btree (group_id)
geobits_location_index gist (location)
geobits_parent_id_index btree (parent_id)
geobits_search_config_index gin (to_tsvector(search_config, raw))
Foreign-key constraints:
geobits_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(id)
geobits_language_code_fkey FOREIGN KEY (language_code)
REFERENCES languages(code) ON DELETE SET NULL
geobits_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
geobits(id) ON DELETE SET NULL
Referenced by:
TABLE geobits CONSTRAINT geobits_parent_id_fkey FOREIGN KEY
(parent_id) REFERENCES geobits(id) ON DELETE SET NULL
Slow Query:
EXPLAIN ANALYZE SELECT id, created_on FROM geobits WHERE
ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(180 90)'),
1000) ORDER BY created_on DESC LIMIT 10;
QUERY PLAN
--
Limit (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.057..4078.090 rows=10 loops=1)
- Sort (cost=205352.10..205352.10 rows=1 width=12) (actual
time=4078.053..4078.064 rows=10 loops=1)
Sort Key: created_on
Sort Method: top-N heapsort Memory: 17kB
- Seq Scan on geobits (cost=0.00..205352.08 rows=1
width=12) (actual time=0.015..3100.471 rows=708661 loops=1)
Filter: ((location
'010120E6100080664000805640'::geography) AND
('010120E6100080664000805640'::geography
_st_expand(location, 1000::double precision)) AND
_st_dwithin(location, '010120E61
00080664000805640'::geography, 1000::double precision, true))
Total runtime: 4078.127 ms
(7 rows)
On Fri, Feb 11, 2011 at 3:42 AM, Mark Cave-Ayland
mark.cave-ayl...@siriusit.co.uk wrote:
On 10/02/11 23:37, Carl S. Yestrau Jr. wrote:
I have a geography(Point,4326) column with a gist index with ~700K
records. A simple query using the ST_DWithin() is taking about 15
secs. Any pointers from the resident experts?
Arch Linux x86
postgis-1.5.2-1
postgresql-9.0.2-2
In order to help, we'd need a lot more information than this - in particular
the schema of the tables (\d in psql output) and the EXPLAIN ANALYZE of the
slow query would be the bare minimum information you'd need to provide.
ATB,
Mark.
--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063
Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_DWithin Performance
I have a geography(Point,4326) column with a gist index with ~700K records. A simple query using the ST_DWithin() is taking about 15 secs. Any pointers from the resident experts? Arch Linux x86 postgis-1.5.2-1 postgresql-9.0.2-2 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_DWithin with radius 0.000001
Hi all At the first time i used ST_Contains but it take a long time, so I used ST_Dwithin to improve the performance for the query statement but i don't know it is correct or not. I would like to know my points are within in polygon or not: SELECT id, geom FROM my_neighbour WHERE ST_Dwithin (geom, *geometry*, *0.1*) *geometry:* is a polygon(input parameter). *0.1:* I means the radius of *geometry* is 1 meters, is it right? Cheers. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : http://axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin with radius 0.000001
Hallo What you are doing with ST_Dwithin is to check if your geom and geometry have parts closer to (or exactly) 0.1 mapunits from eachother. If you are using a meter based SRID then you are checking if they are within 0.1 meters from eachother. So actually in this case your points can be just outside the polygon and returning true. If you want to use st_dwithin you should use 0 instead of 0.1 . But I don't think that is any faster than ST_Intersects. The important thing in both cases is to have working spatial indexes. put an index on geom column in my_neighbour table and analyze the table. Create index idx_mn_geom on my_neighbour using gist(geom); analyse my_neighbour; Then try: SELECT id, geom FROM my_neighbour WHERE ST_Intersects (geom, geometry); I guess that is the best solution. Hope that helps Nicklas 2010-05-25 nguyen liem wrote: Hi all At the first time i used ST_Contains but it take a long time, so I used ST_Dwithin to improve the performance for the query statement but i don't know it is correct or not. I would like to know my points are within in polygon or not: SELECT id, geom FROM my_neighbour WHERE ST_Dwithin (geom, geometry, 0.1) geometry: is a polygon(input parameter). 0.1: I means the radius of geometry is 1 meters, is it right? Cheers. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin with radius 0.000001
Hi Nicklas Actually the points can be outside the polygon and returning true but it can be tolerance about 1 meters outside. I'm using SRID: 4326. I would like to know 0.1 with the projection 4326 is equal 1 meter or not ? I used ST_Intersects (geom, geometry); but it's really slow, I don't know why. I also used ST_Dwithin (geom, geometry, 0) and the result is empty, i couldn't found any neighbour. Cheers On Tue, May 25, 2010 at 3:22 PM, Nicklas Avén nicklas.a...@jordogskog.nowrote: Hallo What you are doing with ST_Dwithin is to check if your geom and geometry have parts closer to (or exactly) 0.1 mapunits from eachother. If you are using a meter based SRID then you are checking if they are within 0.1 meters from eachother. So actually in this case your points can be just outside the polygon and returning true. If you want to use st_dwithin you should use 0 instead of 0.1 . But I don't think that is any faster than ST_Intersects. The important thing in both cases is to have working spatial indexes. put an index on geom column in my_neighbour table and analyze the table. Create index idx_mn_geom on my_neighbour using gist(geom); analyse my_neighbour; Then try: SELECT id, geom FROM my_neighbour WHERE ST_Intersects (geom, geometry); I guess that is the best solution. Hope that helps Nicklas 2010-05-25 nguyen liem wrote: Hi all At the first time i used ST_Contains but it take a long time, so I used ST_Dwithin to improve the performance for the query statement but i don't know it is correct or not. I would like to know my points are within in polygon or not: SELECT id, geom FROM my_neighbour WHERE ST_Dwithin (geom, geometry, 0.1) geometry: is a polygon(input parameter). 0.1: I means the radius of geometry is 1 meters, is it right? Cheers. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : http://axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin with radius 0.000001
Ok The distance of 0.1 degree in east west direction will vary depending on how far from the equator you are. How many points is there in your dataset? Do you see if the index is used? /Nicklas 2010-05-25 nguyen liem wrote: Hi Nicklas Actually the points can be outside the polygon and returning true but it can be tolerance about 1 meters outside. I'm using SRID: 4326. I would like to know 0.1 with the projection 4326 is equal 1 meter or not ? I used ST_Intersects (geom, geometry); but it's really slow, I don't know why. I also used ST_Dwithin (geom, geometry, 0) and the result is empty, i couldn't found any neighbour. Cheers On Tue, May 25, 2010 at 3:22 PM, Nicklas Aveacute;n lt;nicklas.a...@jordogskog.no wrote: Hallo What you are doing with ST_Dwithin is to check if your geom and geometry have parts closer to (or exactly) 0.1 mapunits from eachother. If you are using a meter based SRID then you are checking if they are within 0.1 meters from eachother. So actually in this case your points can be just outside the polygon and returning true. If you want to use st_dwithin you should use 0 instead of 0.1 . But I don't think that is any faster than ST_Intersects. The important thing in both cases is to have working spatial indexes. put an index on geom column in my_neighbour table and analyze the table. Create index idx_mn_geom on my_neighbour using gist(geom); analyse my_neighbour; Then try: SELECT id, geom FROM my_neighbour WHERE ST_Intersects (geom, geometry); I guess that is the best solution. Hope that helps Nicklas 2010-05-25 nguyen liem wrote: Hi all At the first time i used ST_Contains but it take a long time, so I used ST_Dwithin to improve the performance for the query statement but i don't know it is correct or not. I would like to know my points are within in polygon or not: SELECT id, geom FROM my_neighbour WHERE ST_Dwithin (geom, geometry, 0.1) geometry: is a polygon(input parameter). 0.1: I means the radius of geometry is 1 meters, is it right? Cheers. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin with radius 0.000001
Hi Nicklas my dataset about 100.000 rows and after run my query i get about 15.000 rows. I has created index for geom field but it was improve the perofrmance for ST_intersect I used SRID 4326, but I don't know how to convert from degree to meter. Cheers On Tue, May 25, 2010 at 3:43 PM, Nicklas Avén nicklas.a...@jordogskog.nowrote: Ok The distance of 0.1 degree in east west direction will vary depending on how far from the equator you are. How many points is there in your dataset? Do you see if the index is used? /Nicklas 2010-05-25 nguyen liem wrote: Hi Nicklas Actually the points can be outside the polygon and returning true but it can be tolerance about 1 meters outside. I'm using SRID: 4326. I would like to know 0.1 with the projection 4326 is equal 1 meter or not ? I used ST_Intersects (geom, geometry); but it's really slow, I don't know why. I also used ST_Dwithin (geom, geometry, 0) and the result is empty, i couldn't found any neighbour. Cheers On Tue, May 25, 2010 at 3:22 PM, Nicklas Avén nicklas.a...@jordogskog.no wrote: Hallo What you are doing with ST_Dwithin is to check if your geom and geometry have parts closer to (or exactly) 0.1 mapunits from eachother. If you are using a meter based SRID then you are checking if they are within 0.1 meters from eachother. So actually in this case your points can be just outside the polygon and returning true. If you want to use st_dwithin you should use 0 instead of 0.1 . But I don't think that is any faster than ST_Intersects. The important thing in both cases is to have working spatial indexes. put an index on geom column in my_neighbour table and analyze the table. Create index idx_mn_geom on my_neighbour using gist(geom); analyse my_neighbour; Then try: SELECT id, geom FROM my_neighbour WHERE ST_Intersects (geom, geometry); I guess that is the best solution. Hope that helps Nicklas 2010-05-25 nguyen liem wrote: Hi all At the first time i used ST_Contains but it take a long time, so I used ST_Dwithin to improve the performance for the query statement but i don't know it is correct or not. I would like to know my points are within in polygon or not: SELECT id, geom FROM my_neighbour WHERE ST_Dwithin (geom, geometry, 0.1) geometry: is a polygon(input parameter). 0.1: I means the radius of geometry is 1 meters, is it right? Cheers. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : http://axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin with radius 0.000001
Hi Nicklas sr for the mistake ... field but it *was not* improve the perofrmance for ST_intersect Cheers On Tue, May 25, 2010 at 3:53 PM, nguyen liem liemnguye...@gmail.com wrote: Hi Nicklas my dataset about 100.000 rows and after run my query i get about 15.000 rows. I has created index for geom field but it was improve the perofrmance for ST_intersect I used SRID 4326, but I don't know how to convert from degree to meter. Cheers On Tue, May 25, 2010 at 3:43 PM, Nicklas Avén nicklas.a...@jordogskog.nowrote: Ok The distance of 0.1 degree in east west direction will vary depending on how far from the equator you are. How many points is there in your dataset? Do you see if the index is used? /Nicklas 2010-05-25 nguyen liem wrote: Hi Nicklas Actually the points can be outside the polygon and returning true but it can be tolerance about 1 meters outside. I'm using SRID: 4326. I would like to know 0.1 with the projection 4326 is equal 1 meter or not ? I used ST_Intersects (geom, geometry); but it's really slow, I don't know why. I also used ST_Dwithin (geom, geometry, 0) and the result is empty, i couldn't found any neighbour. Cheers On Tue, May 25, 2010 at 3:22 PM, Nicklas Avén nicklas.a...@jordogskog.no wrote: Hallo What you are doing with ST_Dwithin is to check if your geom and geometry have parts closer to (or exactly) 0.1 mapunits from eachother. If you are using a meter based SRID then you are checking if they are within 0.1 meters from eachother. So actually in this case your points can be just outside the polygon and returning true. If you want to use st_dwithin you should use 0 instead of 0.1 . But I don't think that is any faster than ST_Intersects. The important thing in both cases is to have working spatial indexes. put an index on geom column in my_neighbour table and analyze the table. Create index idx_mn_geom on my_neighbour using gist(geom); analyse my_neighbour; Then try: SELECT id, geom FROM my_neighbour WHERE ST_Intersects (geom, geometry); I guess that is the best solution. Hope that helps Nicklas 2010-05-25 nguyen liem wrote: Hi all At the first time i used ST_Contains but it take a long time, so I used ST_Dwithin to improve the performance for the query statement but i don't know it is correct or not. I would like to know my points are within in polygon or not: SELECT id, geom FROM my_neighbour WHERE ST_Dwithin (geom, geometry, 0.1) geometry: is a polygon(input parameter). 0.1: I means the radius of geometry is 1 meters, is it right? Cheers. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : http://axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : http://axonactive.vn Address
Re: [postgis-users] ST_DWithin with radius 0.000001
The polygon you are testing against, is it very complex? How long time does it take? did you analyse the table after adding the index? /Nicklas 2010-05-25 nguyen liem wrote: Hi Nicklas sr for the mistake ... field but it was not improve the perofrmance for ST_intersect Cheers On Tue, May 25, 2010 at 3:53 PM, nguyen liem lt;liemnguye...@gmail.com wrote: Hi Nicklas my dataset about 100.000 rows and after run my query i get about 15.000 rows. I has created index for geom field but it was improve the perofrmance for ST_intersect I used SRID 4326, but I don't know how to convert from degree to meter. Cheers On Tue, May 25, 2010 at 3:43 PM, Nicklas Aveacute;n lt;nicklas.a...@jordogskog.no wrote: Ok The distance of 0.1 degree in east west direction will vary depending on how far from the equator you are. How many points is there in your dataset? Do you see if the index is used? /Nicklas 2010-05-25 nguyen liem wrote: Hi Nicklas Actually the points can be outside the polygon and returning true but it can be tolerance about 1 meters outside. I'm using SRID: 4326. I would like to know 0.1 with the projection 4326 is equal 1 meter or not ? I used ST_Intersects (geom, geometry); but it's really slow, I don't know why. I also used ST_Dwithin (geom, geometry, 0) and the result is empty, i couldn't found any neighbour. Cheers On Tue, May 25, 2010 at 3:22 PM, Nicklas Aveacute;n lt;nicklas.a...@jordogskog.no wrote: Hallo What you are doing with ST_Dwithin is to check if your geom and geometry have parts closer to (or exactly) 0.1 mapunits from eachother. If you are using a meter based SRID then you are checking if they are within 0.1 meters from eachother. So actually in this case your points can be just outside the polygon and returning true. If you want to use st_dwithin you should use 0 instead of 0.1 . But I don't think that is any faster than ST_Intersects. The important thing in both cases is to have working spatial indexes. put an index on geom column in my_neighbour table and analyze the table. Create index idx_mn_geom on my_neighbour using gist(geom); analyse my_neighbour; Then try: SELECT id, geom FROM my_neighbour WHERE ST_Intersects (geom, geometry); I guess that is the best solution. Hope that helps Nicklas 2010-05-25 nguyen liem wrote: Hi all At the first time i used ST_Contains but it take a long time, so I used ST_Dwithin to improve the performance for the query statement but i don't know it is correct or not. I would like to know my points are within in polygon or not: SELECT id, geom FROM my_neighbour WHERE ST_Dwithin (geom, geometry, 0.1) geometry: is a polygon(input parameter). 0.1: I means the radius of geometry is 1 meters, is it right? Cheers. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site
Re: [postgis-users] ST_DWithin with radius 0.000001
HI Nicklas If i use ST_Intersect that take 30s (got 15356 rows) If ST_DWithin: 5s (got 15482 rows) I see ST_DWithin is really faster than ST_Intersect. It got more neighbours but it's not a problem. One more question, if i would like to use ST_DWithin in projection WGS84 and the radius is in 1 meter, how to do that? Cheers On Tue, May 25, 2010 at 4:37 PM, Nicklas Avén nicklas.a...@jordogskog.nowrote: The polygon you are testing against, is it very complex? How long time does it take? did you analyse the table after adding the index? /Nicklas 2010-05-25 nguyen liem wrote: Hi Nicklas sr for the mistake ... field but it was not improve the perofrmance for ST_intersect Cheers On Tue, May 25, 2010 at 3:53 PM, nguyen liem liemnguye...@gmail.com wrote: Hi Nicklas my dataset about 100.000 rows and after run my query i get about 15.000 rows. I has created index for geom field but it was improve the perofrmance for ST_intersect I used SRID 4326, but I don't know how to convert from degree to meter. Cheers On Tue, May 25, 2010 at 3:43 PM, Nicklas Avén nicklas.a...@jordogskog.no wrote: Ok The distance of 0.1 degree in east west direction will vary depending on how far from the equator you are. How many points is there in your dataset? Do you see if the index is used? /Nicklas 2010-05-25 nguyen liem wrote: Hi Nicklas Actually the points can be outside the polygon and returning true but it can be tolerance about 1 meters outside. I'm using SRID: 4326. I would like to know 0.1 with the projection 4326 is equal 1 meter or not ? I used ST_Intersects (geom, geometry); but it's really slow, I don't know why. I also used ST_Dwithin (geom, geometry, 0) and the result is empty, i couldn't found any neighbour. Cheers On Tue, May 25, 2010 at 3:22 PM, Nicklas Avén nicklas.a...@jordogskog.no wrote: Hallo What you are doing with ST_Dwithin is to check if your geom and geometry have parts closer to (or exactly) 0.1 mapunits from eachother. If you are using a meter based SRID then you are checking if they are within 0.1 meters from eachother. So actually in this case your points can be just outside the polygon and returning true. If you want to use st_dwithin you should use 0 instead of 0.1 . But I don't think that is any faster than ST_Intersects. The important thing in both cases is to have working spatial indexes. put an index on geom column in my_neighbour table and analyze the table. Create index idx_mn_geom on my_neighbour using gist(geom); analyse my_neighbour; Then try: SELECT id, geom FROM my_neighbour WHERE ST_Intersects (geom, geometry); I guess that is the best solution. Hope that helps Nicklas 2010-05-25 nguyen liem wrote: Hi all At the first time i used ST_Contains but it take a long time, so I used ST_Dwithin to improve the performance for the query statement but i don't know it is correct or not. I would like to know my points are within in polygon or not: SELECT id, geom FROM my_neighbour WHERE ST_Dwithin (geom, geometry, 0.1) geometry: is a polygon(input parameter). 0.1: I means the radius of geometry is 1 meters, is it right? Cheers. -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line :
Re: [postgis-users] ST_DWithin with radius 0.000001
On May 25, 2010, at 5:43 AM, nguyen liem wrote: One more question, if i would like to use ST_DWithin in projection WGS84 and the radius is in 1 meter, how to do that? As someone else said it depends on the latitude you are dealing with, but you take the circumference of the earth at your location and divide by 360 to get one degree. So at the equator one degree is roughly 69 miles or 111km. So 0.1 is 1m at the equator (but is .8m in NJ for example). hth, charles ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin with radius 0.000001
Hi Charles Thanks for your answer, i can understand a little, i will try to research more. One more time, i would like to say thank you with you and Nicklas. Cheers On Tue, May 25, 2010 at 6:47 PM, Charles Galpin cgal...@lhsw.com wrote: On May 25, 2010, at 5:43 AM, nguyen liem wrote: One more question, if i would like to use ST_DWithin in projection WGS84 and the radius is in 1 meter, how to do that? As someone else said it depends on the latitude you are dealing with, but you take the circumference of the earth at your location and divide by 360 to get one degree. So at the equator one degree is roughly 69 miles or 111km. So 0.1 is 1m at the equator (but is .8m in NJ for example). hth, charles ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Liem Nguyen Developer Axon Active Corp. Direct Line : +84 38 948 4645 Fax : +84 38 811 3489 Mobile Phone : +84 168 994 8897 Skype ID : liemnguyendl2204 Email : liem.ngu...@axonactive.net / liemnguye...@gmail.com Web Site : http://axonactive.vn Address : 87 Thang Long Street,Gate 2 Ward 4, Tan Binh District, HCM city, Vietnam. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] st_dwithin
Hi, I have a database stored in WGS84 EPSG:4326 I would like ti find objects within a distance from a lat/lon point. 1) If i use 1 degree as the distance, will I then get an eliptic search area? 2) Will i find objects on the other side of the Datum shift border if I enter a point like lat=60°, lon= -179,5° with a distance of 1°? 3) Hov do I enter a lat/lon point and a distance in meters? Thanks, Paul ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] st_dwithin
On 11 May 2010 07:34, Malm Paul paul.m...@saabgroup.com wrote: Hi, I have a database stored in WGS84 EPSG:4326 I would like ti find objects within a distance from a lat/lon point. 1) If i use 1 degree as the distance, will I then get an eliptic search area? 2) Will i find objects on the other side of the Datum shift border if I enter a point like lat=60°, lon= -179,5° with a distance of 1°? 3) Hov do I enter a lat/lon point and a distance in meters? If you want to do something that works but is relatively inefficient for 3, you can do the following: ST_DWithin() AND ST_DistanceSphere() distance in meters Of course, you would have to make sure that the search radius is big enough to correspond to your search in meters. Of course, it works only for points due to ST_DistanceSphere. If you want to perform something with any geometry, look at postgis 1.5 and the new geography type. Emilie Laffray ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] st_dwithin
Would something like this be inefficient? I store data in a geometry
type, as I don't really care about the spheroid accuracy, as my
location queries are mostly less than 30 km apart. So...
SELECT ST_AsGeoJSON(location), title, description
FROM event
WHERE ST_DWithin(location::geography,
ST_GeogFromText('POINT(-83.359752 42.5212687)'),4000)
ORDER BY ST_Distance(location,
ST_GeomFromText('SRID=4326;POINT(-83.359752 42.5212687)'))
I cast geom to geog and perform st_dwithin and order by proximity.
Ilya
On Tue, May 11, 2010 at 8:37 AM, Emilie Laffray
emilie.laff...@gmail.com wrote:
On 11 May 2010 07:34, Malm Paul paul.m...@saabgroup.com wrote:
Hi,
I have a database stored in WGS84 EPSG:4326
I would like ti find objects within a distance from a lat/lon point.
1) If i use 1 degree as the distance, will I then get an eliptic search
area?
2) Will i find objects on the other side of the Datum shift border if I
enter a point like lat=60°, lon= -179,5° with a distance of 1°?
3) Hov do I enter a lat/lon point and a distance in meters?
If you want to do something that works but is relatively inefficient for 3,
you can do the following:
ST_DWithin() AND ST_DistanceSphere() distance in meters
Of course, you would have to make sure that the search radius is big enough
to correspond to your search in meters. Of course, it works only for points
due to ST_DistanceSphere.
If you want to perform something with any geometry, look at postgis 1.5 and
the new geography type.
Emilie Laffray
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] st_dwithin in SRID 4269
I am a newbie, could somebody explain what distance transformation should i use to eliminate discrepancy in results for the following 2 queries 1) SELECT * from table1 where st_dwithin(geom1, geom2, distance); *(Note, geom1 and geom2 are in SRID 4269) * 2) SELECT * from table1 where st_dwithin(transform(geom1, 2163), transform(geom2,2163), distance); * Note:Here distance is in meters The things that i understood so far are, distance has to be in the same SRID as the two geoms, I knew distance in meters, so the second query will give me correct results however what transformation should i do in the first query distance value to get the same result as of query 2. I am using PostGIS 1.3.5 *Thanks for your replies..* *-- An Expert is the one who has made all the mistakes that are possible within a narrow field. -- Sachin ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] st_dwithin()
Hi All, I have installed PostGIS 1.2.1 version but I could not able to find out st_dwithin() in it. In which version this function will be available? Thanks Regards, Santosh Gaikwad Senior Software Developer Saama Technologies (India) Pvt Ltd. 6th Floor West Wing, Marisoft III, Marigold Premises, Kalayani Nagar, Pune - 411014. India Phone : +91 20 66071397 Mobile: +91-9422005927 E-mail :[EMAIL PROTECTED] http://www.saama.com http://www.saama.com BEGIN:VCARD VERSION:2.1 N:Gaikwad;Santosh FN:Santosh Gaikwad EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20070529T045803Z END:VCARD ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
