It looks like you are mixing geometry and geography. Try this:
SELECT "regions".* FROM "regions" WHERE (regions.service_area &&
ST_GeometryFromText('SRID=4326;POINT(7.04756999999995 51.17042)')) ORDER
BY admin_area_level DESC, fare_quality DESC, is_operation_region DESC
LIMIT 1
-Steve
On 7/15/2013 11:30 AM, Daniel, de la Cuesta Navarrete wrote:
Hi,
I have a table with a geometry column and around 3000 rows.
The table has a geospatial index in the geometry column (service_area):
CREATE INDEX regions_index ON regions USING GIST ( service_area );
I am doing the following query:
SELECT "regions".* FROM "regions" WHERE (regions.service_area &&
ST_GeographyFromText('SRID=4326;POINT(7.04756999999995 51.17042)'))
ORDER BY admin_area_level DESC, fare_quality DESC, is_operation_region
DESC LIMIT 1
It is taking around 8-9 seconds to perform this query but the problem is
that it is not taking the geospatial index, if a do EXPLAIN of the query
I get:
Limit (cost=138.23..138.24 rows=1 width=130769)
-> Sort (cost=138.23..138.24 rows=1 width=130769)
Sort Key: admin_area_level, fare_quality, is_operation_region
-> Seq Scan on regions (cost=0.00..138.22 rows=1 width=130769)
Filter: ((service_area)::geography &&
'0101000020E6100000FF14A930B6301C40CB4A9352D0954940'::geography)
Is it possible to improve the performance of this query? Why is not
taking the geospatial index?
PostgreSQL 9.1.4 on i686-pc-linux-gnu, compiled by gcc (Ubuntu
4.4.3-4ubuntu5.1) 4.4.3, 32-bit POSTGIS=\"1.5.3\"
GEOS=\"3.3.1-CAPI-1.7.1\" PROJ=\"Rel. 4.7.1, 23 September 2009\"
LIBXML=\"2.7.6\" USE_STATS"
Best
D
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
