Tagged Questions
1
vote
2answers
24 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 ...
1
vote
1answer
43 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 ...
0
votes
0answers
30 views
display route from database(postgresql) on
For first part: Link
i have postgresql9.1,postgis2,pgrouting2 and qgis2.2
this is the second part of what i need to do:
Search in a database (postgresql with pgrouting) for a line from public ...
-1
votes
1answer
93 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
52 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 ...
3
votes
1answer
155 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
47 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
1answer
84 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 ...
0
votes
1answer
237 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
100 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 | ...
5
votes
4answers
92 views
uDig window to change between historical dates?
I'm new to uDig, but I'm experienced Java developer. I have points with temperature measurements that change overtime in SQL database. I would like to give user ability to change between historical ...
1
vote
0answers
60 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') ...
0
votes
1answer
66 views
How to select all cities in Germany from city and country layer?
I have 2 layers: layer_1: countries of europe, layer_2: cities of europe
I want to create (and then save) a layer_3 which would be all the cities of Germany.
Is there any good tutorial of how can i ...
3
votes
0answers
218 views
Select features within a country or bounding box effectively using PostGIS
I imported the planet OSM file into a PostgreSQL 9.2/PostGIS 1.5 server using osm2pgsql. I now want to select certain map features which lie within the bounding box formed by a countries borders, e.g. ...
0
votes
2answers
169 views
PostGIS, distance between two users
I have a simple table (location_test) with three column (name, geom , jid). geom is a geometry column with SRID 27700. I set the user's geometry position with:
UPDATE location_test SET geom = ...
0
votes
1answer
104 views
In QGIS can I show just the lines in a Postgis query? How can I do that?
I have three tables in Postgresql, each table with one layer of lines.
I want to show the path of an initial line to another final. Is it possible executing a SQL query?
Thanks
My three tables are ...
4
votes
1answer
529 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
410 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 ...
4
votes
3answers
1k views
How to join two tables using date, time, lat and lon as common field?
I am sorry if the question is elementary but I did not find any clear discussion or guide to understand how to run a query in postgresql. So that a pointer to another website would be also useful. ...
5
votes
2answers
551 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 ...
2
votes
1answer
181 views
How to filter or query data in GRASS?
I have a PostGIS dataset with just over 2 million rows. Each row has a PostGIS geometry point value and several attributes.
I'd like to do some analysis on certain subsets of the data, but I can't ...
2
votes
2answers
661 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
145 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 ...
4
votes
1answer
286 views
Improve database performance for geospatial
I have a database with osm data imported with osmosis tool. I want to search for the nearest POIs from a given point. For this purpose, I perform this query:
select id from nodes
where ...
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
291 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 ...
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 ...
3
votes
3answers
3k 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 ( ...
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 ...
11
votes
1answer
522 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 ...
0
votes
2answers
866 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 ...
