Tagged Questions
4
votes
2answers
35 views
What are the benefits of using ArcGIS with Postgresql over simply ArcSDE?
I understand that in order to benefit from ArcGIS spatial datatypes (ST_GEOMETRY, versioned geodatabases etc.) ArcSDE needs to be installed - and with the 10.1 client it is automatically installed ...
1
vote
1answer
32 views
How to get polygons from PostGIS table to PHP array best?
Currently I fetch polygons from a database table using ST_AsText(). The results are in text format, like the following.
POLYGON((84 104,212 48,321 122,243 179,275 238,131 240,84 104))
I am not ...
0
votes
2answers
55 views
Cannot insert simple geometry data
I installed PostgreSQL 9.2.4 and PostGIS 2.0.3 on my Windows 8 machine. Then I created a table using phpPgAdmin and added a geometry column using the following statement.
SELECT ...
1
vote
0answers
75 views
PostGIS Better ST_Intersects Query
I am currently using the following queries to return all features that intersect a large and complex polygon. I initially tried to use a single ST_Intersect query but because the number of features it ...
1
vote
1answer
41 views
SQL Command to convert pgRouting result to GeoJSON
My goal is to produce a Web UI for pgRouting using this tutorial. I have already imported my data from an OSM pBF to PostGIS using osm2po. Using the pgRoutingLayer plugin for QGIS, I was able to ...
0
votes
1answer
47 views
ST_Box2D in PostGIS 2.0
I am getting an error trying to create a bounding box of a geometry using ST_Box2D(). Is the function deprecated? If so, what is the equivalent function?
SELECT ST_Box2D(ST_GeomFromText('LINESTRING(1 ...
1
vote
0answers
98 views
Function for Selecting Points in a Polygon
I've been banging on this for a few hours, but since I'm relatively new to PostgreSQL and PostGIS, I can't find the solution. I'm trying to create a stored procedure (function) which will return all ...
2
votes
1answer
91 views
Given a lat/long point, determine if it's land or water?
I have a PostGIS-enabled PostgreSQL 9.1 installation with the 10M physical dataset from Natural Earth imported. It's been converted to 900913, and in QGIS I can visualize the data. So the plumbing's ...
3
votes
2answers
68 views
Traversing a PostGIS table tree
I'm developing a geospatial db for a company that operates at a number of large facilities. Its primary purpose is to help staff locate themselves and equipment within each facilities' multi-storey ...
4
votes
2answers
145 views
PostGIS stored procedures
I am wondering if there are open source applications that make extensive uses of the special functions (the ST_* stored procedures) provided by PostGIS? I found many apps that support storing map data ...
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 ...
3
votes
1answer
119 views
Routing with multiple points
I am using postgreSQL, postGIS and pgrouting. Now I am really confused on how am I going to find the shortest path between multiple points.
I know how to find it between two points through dijkstra ...
6
votes
2answers
256 views
PostGIS SQL query to find a polygon that intersects with a line
I need to build a PostGIS query that would select every polygon inside an area the river is flowing through.
I'd better describe with an example below: There is a river (blue line) flowing through ...
1
vote
1answer
131 views
What are FLOOR and CEILIING in PostGIS designated for?
Just a short question to lighten up my understanding of some PostGIS-code:
Regarding the creation of user-defined grids I often find the expressions CEILING and FLOOR. Like in this non-representative ...
2
votes
0answers
64 views
Problem with SPIT (error while importing layers to POSTGIS)
I was attempting to import a polyline roads layer into postgis, using both SPIT in QGIS 1.7 and postGIS GUI for this purpose. I have received errors through both processes as described here:
SPIT
...
1
vote
0answers
180 views
How to handle postgresql/postgis geometry type errors in a function?
I have created the following function in PL/SQL to make a polygon from a set of linestrings:
CREATE OR REPLACE FUNCTION _myfunction(
_fid integer,
_rid integer
)
RETURNS VOID AS $$ ...
2
votes
1answer
140 views
How to return nearest point to input lat/long with distance?
I am trying to write a query with PostGIS that returns one row where the geometry of that row is the closest to an input point. I would also like to include the distance between these points in the ...
3
votes
1answer
72 views
PostGIS - improving sql statement
I've managed to create the below statement that creates a table based on a point in polygon query. However it creates a column that gives a true/false value which I then delete out all the f values ...
5
votes
1answer
122 views
Join based on maximum overlap in PostGIS/PostGresQL?
I have two sets of polygons in two tables. The sets overlap each other. For each polygon in set A, I would like to get the ID of the polygon in set B that it overlaps the most. I'm using PostgreSQL ...
6
votes
0answers
205 views
Getting ST_Difference between two different tables in postgis [closed]
One table has a series of grid squares in it, and the other has millions of voronoi polygons in it. I need to create a table that has the difference between the two of the them so that I effectively ...
1
vote
1answer
120 views
Why is my PostGIS transformation not working?
I am using a PostGIS query to find the intersection of two streets, and then transform the results into lat/lon. The street database's current SRID is 6856, a projection I added to PostGIS myself; and ...
2
votes
1answer
112 views
How to create a network with all the track points
I have created a sample network through the use of gps visualizer. Than I used the ogr2ogr function to import this data in my database.
Now I have a table which holds all the track points of these ...
5
votes
1answer
169 views
Clarification of Logical Operators in a PostGIS Database
The following query returns this result:
select count(*) from a , b where a.geom && b.geom;
count
-------
66514
On a postgis 2 database.
What does the logical operator AND ...
1
vote
0answers
56 views
Database has duplicate values after running pgrouting
I am a newbie with postgresql and sql statements so maybe it is a trivial question, I'm sorry for that.
I am working on a postgis database and try to use the pgrouting function. My goal is to store ...
3
votes
1answer
91 views
Proximity query approach
Let's say that I have two tables in PostGIS, tracks and cities, each table has two columns, in both cases the columns are called name and way, name is a string, way is a geometry.
I want to retrieve ...
1
vote
2answers
164 views
How to find the center point of MULTILINESTRING?
How to find the center point of MULTILINESTRING, which lies on the multiline?
1
vote
2answers
386 views
How to offset point perpendicular to line direction in PostGIS
I have a postgis table of points, that are sitting on road centerlines (I've linear referenced them). And the second table with road centerlines themselves.
The point table also has a field roadid and ...
4
votes
2answers
400 views
Publish SQL Views from Postgres/Postgis to Geoserver
I've trying and reading how to publish a Postgres/Postgis View in Geoserver. I know that I can create an SQL View in Geoserver, but I'd like to define the View in Postgres, not in Geoserver.
Does ...
3
votes
3answers
329 views
How to Calculate length of polyline geometry for several tables in PostGIS?
PostGIS database contains several geometry (polyline) tables (with names "D1_r", "D2_r", "D3_r"). I calculate length for one "postgis layer" use request:
SELECT
...
2
votes
1answer
195 views
How to create a point table from a remote csv file?
The current approach that I am using to load a csv file into a PostGIS enabled database on a server is the following:
-- create table
CREATE TABLE points(
id int,
lat double precision,
lng double ...
9
votes
4answers
851 views
What is the best way to join lots of small polygons to form a larger polygon?
I have the following layer using SRID 27700 in postgis:
It's every administrative region in the UK, and (as you can see from the colour grouping) each of them has a text field specifying the county ...
17
votes
2answers
1k views
Acquiring ArcGIS-like speed in Postgis
I have been using Postgis 2.0 for 3/4 of a year now and while I really enjoy using it, excessive query processing time has rendered it basically unusable for my use case.
I tend to do heavy ...
1
vote
1answer
196 views
Cordenadas 2d transformacion a postgis desde QGIS
This is a three-dimensional shape file, I checked in ARCGIS, where the shape attribute indicates POINT ZM,
This is the table
fid 0
shape* point ZM
atribut1 A
atribut2 B
ESTE ...
2
votes
1answer
108 views
Postgis nested query trouble
I am having difficulty writing a nested postgis query. I have a single table from which I need to create a view in PostgreSQL.
I have created the view with the following command
create view ...
4
votes
1answer
693 views
Performing Geoprocessing SQL Queries in QGIS from a PostGIS Database
This may be a dumb question, but is there way to run a PostGIS geoprocessing SQL query in QGIS? Specifically, I am thinking of running SQLs such as ST_Union, ST_Split etc etc.
I have tried the ...
3
votes
1answer
179 views
How to update attribute values of points outside an area?
I have two datasets in PostGIS 1.5 (on Postgres 9.1) one of polygons and one of points.
I am hoping to create a SQL command that will select all of the points in a dataset that are not within of the ...
1
vote
0answers
155 views
Add building height from xyz points in PostGIS
I have been trying to get this query right and just can' seem to.
I have a building polygon table with over 7 million polygons, indexed
I then have a grided height XYZ table with attribute 'height' ...
0
votes
1answer
95 views
Postgis reorder mixed up linestring chunks / max_segment_length(linestring)?
I imported ~1000 paths from somewhere into my postgis database to a linestring field.
I had the problem that the paths were divided into chunks, and those chunks were mixed up in some cases.
Suppose ...
4
votes
2answers
432 views
Changing Buffer table geometry dynamically in PostGIS using plain SQL
I have two tables in PostGIS, a point and a point_buffer table. The point table has a buffer_distance field, with a default value say 200. Now I want to change the buffer table geometry whenever I ...
1
vote
0answers
183 views
Manifold 'Link drawing to query' - can I do this in QGIS?
Manifold GIS allows the creation of dynamic drawings linked to queries.
Unfortunately I do not have access to Manifold as my employer refuses to pay for it, so am currently investigating ...
1
vote
1answer
743 views
POSTGIS query of point feature. How to find nearest point to query location?
I am querying a point feature using a POSTGIS request fired in Javascript. The decoded URL looks like this and returns great results for polygon features.
...
0
votes
1answer
180 views
Running database operations against a stored GIS geometry
I'm using Quantum GIS and Postgres with the GIS extensions to store shapefiles that I'd like to run point in geometry and point in union geometries against. While it's easy to save and load the vector ...
0
votes
0answers
51 views
postgis minimum distance among many points [duplicate]
Possible Duplicate:
Nearest Neighbor calculation in PostGIS
Is there a way to achieve the following:
SELECT ST_AsText(a.geometry),
min(ST_Distance(ST_GeomFromText('POINT(10 20)'), ...
4
votes
2answers
3k views
How to convert MultiLinestring to Linestring?
I have a multiline string and want to convert it to a linestring in order to use some functions that only work with linestrings. I used ST_Dump() and got a collection of my geometries. If I merge them ...
0
votes
1answer
134 views
Vertex point is returning None
I have this multilinestring that has 48 NPoints. When I execute ST_PointN(line,5) it returns none. And to continue on, it returns None for everything after index:2 up to index:48. This is how my line ...
1
vote
1answer
325 views
Passing parameters to PostGIS SQL queries in Python: weird behaviour
The following code always goes to the except section and I do not understand why..Any help would be much appreciated.
l = None
c = conn.cursor()
try:
s = "SELECT ST_NPoints(ST_GeomFromText(%s))"
...
1
vote
1answer
158 views
How to correctly use cursor.execute() with PostGIS?
I do not understand how the operator works some times...
Here is an example that works,
"SELECT ST_Distance(ST_GeomFromText('POINT(-3.16496271127842 ...
6
votes
2answers
624 views
Join intersecting lines with PostGIS
This is something that I'm almost ashamed to ask but I can't seem to get it to work for the life of me.
I have a road layer with segments, each segment has a Road ID and a segment type.
I would like ...
0
votes
1answer
85 views
How to query distict row with the same label from different linestring with multiple segment?
I have a multilinestring street table in postgis with two columns: label and prefix.
This table has 2 different roads with the same name and each road contains multiple segments.
Roads were splitted ...
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 ...
