2
votes
1answer
42 views

How to extend a straight line in postgis?

I have a postgis table of simple linestrings, each one is a straight line with just two points. I want to select every linestring, but extended from both sides by 1 meter. So i have lines, that are ...
0
votes
1answer
22 views

How to export query result on raster table in PostGIS

I did success query on a raster table in postGIS(raster data clipped by a vector polygon), now I want to view the query result in QGIS. But how to export the query result to another table. In vector ...
0
votes
3answers
59 views

postgis insert query to store geometry and other data

I'm using Postgis functions st_dump and st_split to split a polygon based on line. Now, after splitting; output geometry stores in a database table as well. Here is the query: insert into aoo(geom) ...
0
votes
1answer
33 views

PostGIS Group By Error [closed]

I am performing a pretty simple query in PostGIS 2.1.1 from within QGIS 2.2 for Windows (using the DB Manager plugin). I have a table with many columns and I am trying to perform a Group By function. ...
0
votes
1answer
45 views

Extract address data / Query point in administrative boundary

I try to create a DB in postgreSQL with all the postal adress data for a specific country. Actually I have this table full of data: id integer NOT NULL DEFAULT nextval('plz_id_seq'::regclass), ...
3
votes
1answer
237 views

Problem PostGIS Spatial Query ST_Contains ST_Disjoint ST_Intersects

I'm working on a project with two different tables inside a database. One contains points in a column geometry(Point,4326) type and the second one has a column geometry(MultiPolygon,4326). Now I would ...
14
votes
1answer
346 views

How to query OSM for all beaches facing west? - QGIS preferred

I want to identify all beaches on this planet (or a given subset of it) that are facing westwards (+/- 20-30 degrees). My idea is to use OSM as the data source. Extracting all polygons that have the ...
1
vote
2answers
169 views

Searching planet_osm_point by longitude and latitude

I used osm2pgsql to import data into my PostgreSQL database and it put gave me the following 4 tables, planet_osm_point, planet_osm_line, planet_osm_polygon and planet_osm_roads. I have looked at ...
2
votes
1answer
90 views

Area of polygon split by a line

I need a query that return the area of two polygons that have been split by a line. This is what I did so far: SELECT ST_Area(ST_Split(surface.geom, limite_a.geom)) from surface, limite_a where ...
0
votes
2answers
35 views

How can I optimize this point-linking query?

I have the following query, and am wondering how it could be optimised to run faster: WITH gps_nodes_1 AS ( SELECT "NODE_ID", "geog" FROM gps_nodes WHERE ST_Distance("geog",ST_GeogFromText('POINT( ...
2
votes
1answer
136 views

ST_Intersects() in WHERE clause misses results

I have a table with a lot of geographical objects like countries, cities or points inside the cities. For instance # SELECT id, ST_AsText(location) FROM brace_brace WHERE id IN (3, 42); id | ...
1
vote
0answers
20 views

Forcing Hash Join Query Plans using PostGIS

I'm currently testing PostGIS 2.0 in a Greenplum Appliance (Postgres 8.2 based) and have hit an incompatibility between PostGIS and the MPP Greenplum environment. The basic query below can't be ...
1
vote
1answer
62 views

Problem with runing a loop in a query in PostGIS

I am new to PostGIS and I have a question which can't solve. I have a table of buildings each having a value for number of people living in it. I would like to calculate the total value (sum of number ...
-1
votes
1answer
144 views

Shortest Path Dijkstra

I would like to calculate the shortest path with dijkstra algorithm. sample query SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra(' SELECT gid AS id, ...
0
votes
1answer
85 views

How to cut/split multipolygon with blade (linestring) into two pieces using PostGIS? [closed]

I struggle to do split operation in postgis I tried to build a query but Im always fail. Id like to cut a multipoligon with a blade (a linestring) into two pieces. These two objects have specific ...
1
vote
1answer
50 views

Rewrite query to match two points with a linestring in a different database table

OK, not sure if I've worded the title brilliantly, but here goes: Suppose I have: a table "gps_nodes" containing columns: "EASTING" (containing longitude data) "NORTHING" (containing latitude ...
3
votes
1answer
382 views

PostGIS intersection function

I have a problem with intersection function. I built my query following http://manual.linfiniti.com/en/postgis/geometry.html, 5.7 section. I made my clip object, a red linestring frame: But after ...
0
votes
2answers
55 views

Select area which is at least x meters away from other geometries

In my table I have the build-up area from a region as a big number of polygons. urban_area: id | name | geometry(polygon) I was wondering if and how I would be able to find the area within a ...
0
votes
0answers
75 views

Find area with highest relative density of one type of object

I'm using PostgreSQL/PostGIS and I have an advanced type of query. My database contains two types of object - let's say pubs and churches :) Is there a way that I can find the area with the densest ...
8
votes
1answer
239 views

What is the precision of SELECT DISTINCT on PostGIS geometry column?

I wonder what the precision of the SELECT DISTINCT operator is on a PostGIS geometry. On my system, the following query gives me a count of 5, which means that the inserted points are considered equal ...
0
votes
1answer
119 views

Defining a WHERE clause from a subquery for a query to create a handy view

It is about my implementation from an answer of this question. It is more an postgresql issue than a GIS issue, whereas it is about geometries and spatial queries. I want to create a view from the ...
2
votes
2answers
459 views

Postgis, get the points that are x meters near another point, in meters

I'm trying to find the points within a certain distance, in meters, from another point. I'm using the function ST_Buffer, but I don't get how to express the distance in meters. I'm trying with ...
0
votes
1answer
284 views

PHP/MySQL, PostgreSQL spatial queries. Find a polygon that contains a coordinate? [closed]

I am building a non mapping javascript app and I am wanting to take a lat/long coordinate and query a MySQL table of polygons for the one that contains the point. I have looked into MySQL spatial ...
1
vote
1answer
115 views

Computing altitude from contour table - slow ST_Dwithin query

I am using PostgreSQL 9.2/PostGIS 2.0. I have a table of house addresses and a table of contour lines which are as follows: address_list house_id | geom (point, 4283) ---------+--------- 1 | ...
1
vote
0answers
80 views

PostGis search query on hstore tags

I am writing a search client, to query postgis db. The search query will be, SELECT tags->'name' AS name,ST_Transform(way,4326) AS pt_lonlattext FROM planet_osm_point WHERE lower(tags->'name') ...
1
vote
2answers
709 views

spatial query st_contains postgis

This seems too simple to be asking this but I am really new to postgis/postgres have been going around in circles with this. I am performing a really simple spatial query where I would like to make a ...
0
votes
1answer
153 views

Circles created by points inside bounding box query - PostGIS

The table that i want to perform this query has 3 columns: id, the_geom and seen_count. The query i've been using to get points inside a bounding box is as follows: SELECT id, ST_AsGeoJSON(the_geom) ...
1
vote
2answers
437 views

ST_DWithin uses Geometry or Geography type

assuming I use the following query to match the locations to a poi within 10km distance: SELECT poi.poi_id, h.loc_id, (ST_Distance(poi.geom,h.geom)*111.111) as distanceinkm FROM iut_poi_transl AS ...
2
votes
1answer
314 views

Help building SQL query to get a list of streets of a city

From two layers in PostGIS (osm_places and osm_roads), I would need to have the roads belonging to a certain city. It would be fair enough to get the street names of all streets in a radius of X ...
4
votes
1answer
732 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
615 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
83 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
163 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
759 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 ...
10
votes
3answers
793 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
393 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
949 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
153 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 ...
7
votes
3answers
212 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 ...
4
votes
1answer
1k 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 ...
8
votes
3answers
387 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
867 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
292 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
2k 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
760 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: ...
5
votes
1answer
2k 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
1k 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) ...
4
votes
3answers
4k 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
512 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
2k 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 ...