Tagged Questions

3
votes
1answer
81 views

How to Find points in a Kilometre radius

I am new to PostGIS and GeoData. I need to find where the lightning has stroke by a period of time and kilometre radius based on a given location. I have given lon/lat and my table is: latitude ...
4
votes
1answer
154 views

How to speed up an st_intersects() query?

I have a "huge" spatial database: 72GB, 200 millions documents with geometries (points) I have a GIST index on the geometries the database: CREATE TABLE resources ( id bigint NOT NULL, accuracy ...
1
vote
1answer
52 views

How to get routes in the database that intersects circle?

I have routes (linestrings) recorded in the database. I want to create a query which returns the routes that have some commonality with a circle whose center and radius are passed as parameter. I ...
1
vote
0answers
81 views

Query to find lat/lon of a house by street.name & house.number

How can I get house coordinates (latitude, longitude) by street.name and house.number? If it is possible, how I can get it? Many thanks!
5
votes
2answers
146 views

PostgreSql query problem with aggregate functions

I need help with a PostgreSql query. I need to find all parcels which have bigger area then average. I'm using this query: SELECT OBJECTID FROM parcela WHERE ST_Area(geom)<(SELECT ...
8
votes
3answers
249 views

How to speed up queries for raster databases?

I have a raster database in postgresql/postgis with these columns: (ID, rast, data_of_data). 'rast' is the column that has raster files in WKT format. An example query to find the DN value of a ...
2
votes
2answers
240 views

How fill length and feature count in PostGIS table for each polygons

I'am new to PostGIS/PostgreSql. I have a database which containts two shape-files : polygonal layer towns and linear layer of roads To calculate the length and feature count of 'Streets' inside ...
2
votes
2answers
161 views

Spatial index to speed up the query

I have spatial table in PostgreSQL 9.2 / postgis 2.0 that I have to extract some information from it. But my query is hopelessly slow. How could I make use of spatial index to improve the performance ...
3
votes
2answers
126 views

Multiple queries in a row

I have osm data imported into PostgreSQL with osm2po tool (wich makes the data routable). Also I have imported data relationed with nodes and node_tags with osmosis tool. I need to obtain a set of ...
6
votes
3answers
163 views

What would be 'normal' processing time for a large postgis query

I have two datasets which I want to spatially join (with an intersect). The first dataset consists of 9 million addresses. The second dataset consists of 12.000 neighbourhoods. I want the add the ...
2
votes
1answer
581 views

How to find the nearest point by using PostGIS function?

table A: lat | long | the_geom | code | sign 13.8433095 | 100.6360357 | 0101000020E61.... | ABC | start_point 13.7544738 | 100.5459646 | 0101000020E6..... | ABC | end_point 13.4124215 | 100.6232332 ...
6
votes
3answers
144 views

How to select only Spatial tables from the postgres database?

My database contains both Spatial and Non-Spatial tables, but I want to retrieve only Spatial table from the query. Any suggestions to select only Spatial tables. 'the_geom' is the geometry column in ...
1
vote
0answers
462 views

How to fire a query from OpenLayers to GeoServer to fetch 'the_geom' column in PostGIS table?

I am coloring the polygon shapes of world_country. I have OpenLayers in my UI, GeoServer as server and PostGIS as db. All I want is fire this query: select the_geom from world_country where ...
1
vote
2answers
197 views

postgis, mapserver & openlayers: if data is not in table give an error / pop-up

I have created a website with postgis, mapserver & openlayers. On the website you can enter a postal code and then a drivetime isochroon is created. The problem now is that you can enter ...
3
votes
1answer
686 views

How to filter features in a layer?

I am trying to display certain features in a shapefile-based layer using QGIS. I have found the subset function in the properties panel, which does exactly what i want, but is too tedious given that I ...
2
votes
1answer
517 views

Why does my PostGIS query fail with “current transaction is aborted”

I'm trying to create a vector layer in a QGIS project using a PostGIS query. QGIS is returning the following error: Query failed 1 cursor states lost. SQL: CLOSE qgisf1 Result: 7 (ERROR: ...
3
votes
1answer
796 views

How do I perform a proximity search with Postgis?

OK, so I downloaded the Geonames database (cities1000) and wrote a small Ruby program to import them into my table (geo_cities). I then added geography column called geog. So after all of that, I ...
2
votes
1answer
592 views

PostGIS UPDATE Statement with ST_Contains(table1.geom, table2.geom)

I'm strugeling with a query I should write to UPDATE my dataset. First of all, I have 2 tables containing data and I need to mix it. Tabel 1 -- lets call it names: name_str the_geom (points) ...
3
votes
3answers
2k views

How to fix performance problem in PostGIS ST_Intersects?

I'm a newbie in postgis and I have a problem in query performance. This my query: SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp FROM table1 WHERE ST_Intersects ( ...
6
votes
1answer
315 views

How to sum area of polygons by values occuring in multiple fields?

My data has 2 fields holding landuse types, which both describe what is within the polygon. For example, the polygon may have Type1=Retail, Type2=Leisure Area= 1 but another polygon has Type1=Leisure, ...
2
votes
1answer
1k views

Basic MapServer query of PostgreSQL/PostGIS database

I've just installed MapServer on a linux server and I am trying to understand how to display a basic map using a PostGIS database. I have displayed a shapefile in the browser, so I know that the ...
11
votes
1answer
407 views

Nearest Neighbor Grouped Selection in QGIS

OK - so I have an interesting problem I am trying to solve. I have a list containing over 100,000 points in lat/long format which I have imported into qgis. Now, what I am trying to do here is group ...
5
votes
2answers
448 views

Practical way of managing polygons and unions of them

I have a table in PostGIS with a quite fine partition of a region, which I'd like to dissolve at two higher levels by means of two foreign keys (e.g. municipality and state). The partition is a real ...
8
votes
1answer
449 views

ST_ClosestPoint(line, point) does not intersect line

In my PostGIS database (PostGIS 1.5 on Postgres 8.4.1), I have two tables: roads (composed of linestrings) and crashes (composed of points). I've been trying to correlate each crash to a road, but I'm ...
0
votes
2answers
614 views

QGis import partial PostGIS layer using query builder

I'm sorry, but I'm terribly new to the GIS scene (and PostGIS & QGis). I have loaded a whole bunch of OpenStreetMap data into a PostGIS database and successfully drawn a map using Mapnik. Now I ...