Version 2.0 of PostGIS brought a heavy set of new extensions for the PostgreSQL object-relational database, adding greater support for spatial objects.

learn more… | top users | synonyms

1
vote
0answers
21 views

How to add fields to postgis layer loaded in qgis using python

I have loaded a layer from Postgis in qgis and I am adding the fields to the layer using the follo code snippet: canvas = qgis.utils.iface.mapCanvas() aLayer = canvas.currentLayer() field_name = ...
0
votes
1answer
31 views

ST_Union or ST_Collect on PostgreSQL union result?

Newbie to PostGIS/Postgres. I have the follow query which returns me 2 GEOMETRYCOLLECTIONS. Is there a way within my current query to return one polygon? SELECT ...
1
vote
0answers
18 views

Swapping start and end points of line (i.e. direction) of LineString in PostGIS?

I know that postgis has the function to find the start and end point of a line. Is there a function that can swap the start point and end point? (Note: I do not mean literally changing the ...
3
votes
1answer
35 views

unable to render/view a postgis raster in qgis 2.14.1

I have successfully added a raster to postgis using the following: raster2pgsql -s 4326 -I -C -M 00N_030E.tif -F -t 250x250 -a test.00N_030E | psql -U postgres -d raster -h localhost -p 5432 I ...
0
votes
0answers
6 views

Raster ranks in Postgis-ST_ColorMap

Rather than categorizing raster in *% form, giving rank values is possible in ST_ColorMap?, like [value1-value2] 200 0 0 "SELECT ST_ColorMap(the_raster,'100% 255 0 0", " 75% 200 0 0", ...
2
votes
1answer
35 views

Loading Data with shp2pgsql and “Syntax error at or near shp2pgsql”

I want to import a shapefile using shp2pgsql function in the psql shell on Mac. I have created a spatial database (example_gis). By refering to this guide, having connected to this database, when I ...
1
vote
1answer
45 views

PostGIS: how to merge contiguous features sharing same attributes values?

I have a big postgis table storing line features. I woud like to merge features that are both connected to eachother and have given attribute values identical. Here is below an example of what I'd ...
1
vote
1answer
46 views

Using Postgis spatial index in st_distance operation based upon explicit SRID column

I have two large tables with data that spans over multiple regions of the U.S and needs a defined SRID column in atleast one the tables to denote which coordinate system should be used for ...
4
votes
2answers
107 views

PostGIS Geography Buffer producing different areas?

I'm trying to create circles of equal area in different places, but depending on the location of the centroid different areas are calculated. For example SELECT ST_Area(ST_Buffer('POINT(0 ...
0
votes
1answer
62 views

Alpha Shapes in pgRouting

I am attempting to follow Anita Grazer's post on creating alpha shapes in pgRouting. My query: CREATE TABLE home_isodist (id serial, max_cost double precision); SELECT ...
2
votes
1answer
53 views

Paste a feature from an existing shapefile into a row with null geometry in QGIS

If I have an existing row in my PostGIS database with NULL geometry, is it possible to select a feature from another layer in QGIS, and paste that into the empty geometry column for the PostGIS row?
1
vote
0answers
37 views

update query with st_intersects() is not finding results

So I am trying to identify which items from the requests table fall into some particular district from the district table using postgis. Basically, I am writing an UPDATE query that will take the ...
2
votes
0answers
41 views

TopoGeometry commands with postgres are really slow

After messing around quite a while, I was finally able to get a topogeometry converted from my geometry table following the example of toTopoGeom. I have the topology extension, with a topology schema ...
1
vote
1answer
26 views

PostGIS update data with ST_Tile

I need to update tiled raster data from the "whole image". I have created this query: UPDATE data_tiled AS t SET raw_data = f.raw_data FROM ( SELECT data_time, data_type, ST_Tile(raw_data, 250, ...
0
votes
0answers
35 views

PostGIS tiger geocoder installation trouble

I am getting the TIGER geocoding feature set up on my windows 7 64 bit box (PostGIS 2.1; Postgres 9.4). I followed the steps outlined here to generate the batch scripts and ran them last night: ...
1
vote
1answer
94 views

pgrouting tsp (travelling salesman problem) performance

I currently use a web API to solve TSP tasks. However my provider limits the query to a maximum of 150 stops. I therefore consider to host my own routing service based on OSM data and pgrouting. My ...
3
votes
1answer
113 views

Distance in meters between two geographic coordinates

I want to determine the distance in meters between two geographic coordinates. The points are: Lat (deg) Lon (deg) A: -33.3930906 -70.5452765 B: -33.3993212 -70.5468767 So if i calc the ...
2
votes
0answers
74 views

Dijkstra's Algorithm direction issue

I am trying to implement Dijkstra's algorithm, my query is working fine if I set direction parameter to false. I have to consider the direction of travel while calculating route from point A to point ...
2
votes
2answers
44 views

PostGIS - raster and bands

I have several images from different times and for every time I have three types of image (temperatureA, temperatureB, pressure). Should I store them as every time as band in a single raster with N ...
1
vote
1answer
63 views

postgis - wrong ST_Distance with geography points [closed]

I'm trying to calculate the distance between to geographical points, but the calculated distance is bigger than the real one (from Google Maps). The location column in postgresql is define as ...
2
votes
0answers
39 views

Add bands or new raster column in Postgis?

Would it be faster to work with a new band or a new raster column for a raster dataset in Postgres with new pixel values, assuming that the size, extent and everything else relevant remains constant? ...
3
votes
2answers
319 views

Is there a way to change ST_asGeoJSON return values?

The function ST_asGeoJSON returns something like: "st_asgeojson": { "type":"MultiLineString", "coordinates":[ [ [-3.13512629299993,39.7174353130001], ...
0
votes
2answers
59 views

Finding vertex in comon with most polygons in PostGIS?

I have a table with polygon geometry, in this case representing counties. I'm trying to figure out how to write a query that returns vertices sorted by the number of polygons that share that vertex. ...
0
votes
1answer
92 views

transform geometry PostGIS column with unknown geometry type

I run PostgreSQL 9.1 and PostGIS 2.0. I have a table that contains one geometry column. But that column contains point, polygons lines, etc. So I set it like so, ALTER TABLE jake ADD COLUMN ...
1
vote
0answers
41 views

Resuming an osm2pgsql import to postgres [closed]

I was doing an import of the north american data with osm2pgsql using sudo ./osm2pgsql -c -S /home/osmNA/nfs/osmNA/osm2pgsql/default.style --slim -d addressNA --flat-nodes flat-nodes.bin ...
0
votes
1answer
48 views

How do I intersect a point with a shape?

I have a table in Postgresql / PostGIS containing county geometries. This table was created by importing a shapefile. I verified the geometry was imported successfully by importing the table into QGIS ...
2
votes
1answer
94 views

Postgis: intersection between geometry and geography

I am trying to detect if a geometry and a geography intersect or not. The problem is I cannot find such a method. There only is: boolean ST_Intersects( geometry geomA , geometry geomB ); boolean ...
0
votes
1answer
92 views

function st_length3d(geometry) does not exist

Whenever I execute this query: SELECT ST_Length3D(ST_GeomFromEWKT('LINESTRING(0 0 0,5 0 3,5 10 5)')); Postgresql is throwing an error: ERROR: function st_length3d(geometry) does not exist SQL ...
1
vote
0answers
295 views

PostGIS is already installed in schema 'public', uninstall it first

I am having problems adding postgis to my database. I'm following the instructions from here: http://postgis.net/docs/postgis_installation.html#idp32328352 (That said, I have combined some previous ...
0
votes
1answer
208 views

How to check overlap between two sets of Linestrings using postgis

I have 2 sets of LINESTRINGS, each set containing around 50 LINESTRINGS(which form a road), how do I check whether these two sets overlap or not ? Thanks in advance.
2
votes
1answer
39 views

postGIS SQL request with aggregating ST_Union()

I have a postGIS table that looks like this : id | value | geometry(MultiPolygon) 1 10 ... 1 20 ... 1 30 ... 2 10 ... 2 20 ... 2 30 ...
1
vote
1answer
93 views

A few PgRouting database design questions

Does someone know how the cost field in PgRouting is calculated? What are 'source' and 'target' good for? 'osm_source_id' and 'osm_target_id' contain the same information using different numbers. Is ...
0
votes
1answer
58 views

pgsql2shp error: table does not exist

I am exporting tables from Postgresql 9.2 to shapefiles using postgis 2.0. I have the same system on other machines and it works correctly. I have tried running the command from a prompt and get an ...
1
vote
0answers
103 views

Can QGIS handle EMPTY geometries?

I am writing a plugin and have a postgis table which has EMPTY geometries in the geom column. MULTILINESTRING EMPTY I am having crashes which just close QGIS. I have narrowed it down to the EMPTY ...
1
vote
1answer
112 views

Set Tolerance in Postgis

is there anyway to set tolerance in Postgis, in Arcgis tolerance is the minimum distance which could separate two points, if the distance between them is smaller than the tolerance so the points are ...
3
votes
2answers
1k views

Cleaning geometries in PostGIS?

I am trying to do some processing on some very large polygon layers. However I am running into various geometry errors such as: NOTICE: Ring Self-intersection at or near point 470396.52017068537 ...
0
votes
1answer
90 views

ST_Dwithin Source Code

I am looking for the st_dwithin postgis source code. I want to learn the way postgis determines whether a given geometry is within a given radius to another geometry.
0
votes
1answer
77 views

Problem importing sqlite into Postgres (with PostGis) using ogr2ogr

I am trying to import a very large sqlite file into Postgres SQL 9.3. The first tool I tried to use was OGR2GUI (http://www.ogr2gui.ca/). The 64 Bit version does nothing, whilst the 32 bit version ...
3
votes
2answers
322 views

Trouble using ST_Difference to remove overlapping features

I am trying to use ST_Difference to create a set of polygons (processing.trimmedparcelsnew) that do not contain any of the area covered by another set of polygons (test.single_geometry_1) using ...
4
votes
1answer
255 views

Updating SRID (Update_GeometrySRID) does not alter polygons (when it should)

I am trying to change the SRID of polygons layer, but I'm having trouble visualizing the result. I the SRID is misreported the shapefile I used to import into PostGIS. The metadata says the ...
8
votes
1answer
186 views

Building a polygon over a reachable area

I am currently working in the field of isochrones and the underlying algorithms. What now causes problems is not the calculation if the isochrone itself, but the visualization of the results. The ...
1
vote
0answers
80 views

What is the fastest way to select oldest, unique geometry from a table containing duplicates in PostGIS?

I have an output from an ArcPy arcpy.Union_analysis operation and for the overlap polygons that were created I need to select out the oldest feature (along with the attributes). The result is 75740 ...
2
votes
0answers
83 views

PyQgis - Adding attributes to a POSTGIS layer

After reading @underdark's answer to this question on creating a new vector layer and @Matthias Kuhn's to this one on field calculation, I wrote a script that allows me to calcultate the mean ...
1
vote
0answers
30 views

find nearest point to a point in another table

This similar, but not quite the same as this question. I need to pick, regardless of distance, the closest 'n' points to a given point in another table, using postGIS. Imagine a table with city ...
0
votes
1answer
24 views

PGR_Djikstra function error

I'm trying to find a shortest path between 2 nodes using djikstra method but it keeps saying ERROR: Error, query must return columns 'id', 'source', 'target' and 'cost' this is my query: ...
1
vote
1answer
379 views

connecting Arc 10.3 to a PostGIS database “the coordinate system identifier is invalid” [closed]

Has anyone had any success directly connecting Arc 10.3 to a PostGIS/postgres database, where the geometry type is 'geometry'? I am able to connect and view the tables, but I am unable to view them - ...
0
votes
0answers
164 views

Why is Geoserver publishing my WMS layer in WGS84, when I have specified WyLAM?

I am trying to publish a WMS layer from Geoserver, based on a table in a PostGIS dastabase. The projection of the original source data is "WyLAM" (EPSG:8155). It seems that no matter which options I ...
2
votes
2answers
116 views

Is it possible to georeference raster stored in Postgis?

I'm looking to store a huge raster aerial image (400 Gb) into Postgis, but before storing it i like to know how i could georeference or rectify raster stored in postgis ? is there any Qgis plugins ...
0
votes
1answer
76 views

how to insert coordinate of province to postgresql?

i need insert to database this area sample: [ [ [ 50.227886556627098, 31.15709614260982 ], [ 50.227886335531331, ...
0
votes
0answers
53 views

How do I design a database for storing GPX tracks and supporting spatial queries? [duplicate]

I have a series of GPX files that contain 1...n tracks. Each track contains roughly 5k - 10k points. The GPX files do not contain waypoints or routes. I want to design a database with the following ...