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
1answer
695 views

PG::UndefinedFunction: ERROR: operator does not exist: point = unknown

Recently, I developed a feature to save and fetch points(lat and lon) from DB. It is working fine on my local, but on production database it is giving error: PG::UndefinedFunction: ERROR: operator ...
2
votes
2answers
93 views

Create GeoJSON with CRS from Postgis

I have a table of Multiline Strings and need to generate a GeoJSON with the properties turning to EPSG 4326. I made the SQL below but is not in the correct format GeoJSON. How do I create the GeoJSON ...
1
vote
1answer
40 views

Can the correct TIGER Line Id be returned for an address from the PostGIS Tiger Geocoder?

After reviewing the PostGIS Tiger Geocoder documentation, I don't see a method of returning the TIGER Line Id for the address. I would assume this is information is available internally as part of ...
0
votes
1answer
41 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: http:/...
3
votes
3answers
2k views

How to find polygons inside polygon with PostGIS 2.0

I have a relation representing all the counties in my country and the other one containing all the districts - e. g. each and every county is composed of several districts. How do I select them with ...
1
vote
0answers
27 views

PostGIS Nearest Neighbor query performance - can it be quicker?

I want to canvas the community to see if my nearest neighbor query can be optimized at all. I am happy with my current results, but I'm always up for improving. I have 2 tables, lakes and towns. I ...
0
votes
0answers
17 views

Error connecting to PostgreSql 9.2 using Autocad Map 2011

I want to connect Postgresql 9.2 database (installed at localhost , port-5432) from AutoCad Map 2011 (installed in same PC). I have successfully copied all required dll files (total 13 nos.) from ...
3
votes
1answer
27 views

Problems with dividing multipolygons into polygons

I have a shapefile with countries boundaries downloaded from here and I am trying to upload it to PostGIS database with ogr2ogr. This file contains features of polygon and multipolygon types and ...
2
votes
2answers
1k views

How to connect Postgis database with/in Remote Servers…?

I am preparing a 'Web based map' application using 'Geoserver-Openlayers-Postgis'. I am trying to connect my Postgis database(located at 10.50.110.11 location) with the following command from 10.40....
1
vote
2answers
239 views

Loading data using QGIS to PostgreSQL database

I am trying to import a shapefile using desktop QGIS to postgreSQL database using the import shape file to postgresql database tool but it raises the following error: Problem inserting features ...
1
vote
0answers
25 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
40 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 ST_AsText(ST_Collect(ST_Simplify(geom,....
9
votes
3answers
1k views

Why is any pgr_* routing function taking forever based on OSM data in an pgrouting enabled DB

I loaded the German OSM dataset into the pgrouting DB by using osm2po 4.7.7. Everything works fine i have osm2po set up via it's config and it's working like a charm through it's Java part. I had the ...
0
votes
1answer
246 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.
6
votes
3answers
828 views

Very poor performance of QGIS and PostGIS2. User error or feature? [closed]

I have a large table in Postgis2. It has >500 million polygon, line and point features all happily living in there and the table performs brilliantly in other GIS. I am now trying to just add that ...
1
vote
0answers
26 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 co-...
3
votes
1answer
47 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
12 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
44 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 ...
2
votes
1answer
40 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
48 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 ...
4
votes
1answer
627 views

Using PostGIS for a large trajectory dataset

I have a spatial trajectory dataset which basically contains about 10E6 (lat,lon,time)-tuples, grouped together by an identifier so they form trajectories. Currently, SQLite is used to store the raw ...
0
votes
1answer
525 views

TinyOWS failing to connect to postgresql - Ident authentication failed for user “postgres”

I'm getting the above error on a new and local TinyOWS installation on Centos 6. Postgresql 9.2, PostGIS 2.0. I can connect to PostGreSQL locally and from my LAN using several other methods (psql, ...
4
votes
1answer
482 views

How to Load Relational Database Format Data into PostGIS?

I want to load some sample Relational Database Format Data to POSTGIS. Are there any tools or methods to do this?
4
votes
0answers
274 views

PostGIS 2.0 st_geomfromgml using LineString fails with invalid GML representation

I am attempting to insert a geometry into a table using the st_geomfromgml function. The GML is being sourced from a Geoserver WFS request. The function call looks like this st_geomfromgml('<?...
1
vote
1answer
64 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 area/...
4
votes
2answers
108 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)'::...
0
votes
1answer
65 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 addgeometrycolumn('...
8
votes
0answers
234 views

Joining layers with respective elements with PostGIS Topology

I'm currently using PostGIS topology extension, but I have some difficulties in understanding how the structure works: One of the key points is the use of "layer"s: as of what I understand, features ...
8
votes
1answer
184 views

Why are date vaules displaying a trailing 'Z' when layers are published with Geoserver and a PostGIS data store?

I have published various layers using Geoserver 2.2.x with a PostGIS 2.0.1 (PostgreSQL 9.1.x) data store. Fields that only have date values and not time/timestamp related values are stored as date. ...
2
votes
1answer
193 views

St_OffSetCurve with self-crossing lines

I have about 50,000 linestrings in PostGIS. They represent routes along roads. But I want them to be (roughly) pavements (aka sidewalks) instead. So I need to move all the lines 3 metres to the left ...
2
votes
1answer
57 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?
2
votes
0answers
128 views

Is the postgis layer option in qgis 2.12 Lyon two way? Was the spit plugin built in?

I am trying to install Postgresql with Postgis on a remotely accessed terminal only installation of Ubuntu 14.04 that I want to use to connect to QGIS 2.12 and hopefully have a two way transfer ...
1
vote
0answers
38 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
47 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 ...
0
votes
1answer
239 views

How to create buffer around point having projection 4326

I am trying to create buffer (in meters) around point(long lat alt) which is in projection 4326 with below code. But it is not giving expected output. SELECT ST_AsTEXT(ST_Buffer((ST_GeomFromText('...
1
vote
1answer
30 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, ...
3
votes
1answer
1k views

Error saving polygon with hibernate spatial and postgis

I'm trying to persist a com.vividsolutions.jts.geom.Polygon in my PostgreSQL (with PostGIS enabled) database, using Hibernate and Hibernate-Spatial plugin. Caused by: org.postgresql.util....
1
vote
1answer
98 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 ...
8
votes
5answers
3k views

How to split OSM roads into individual segments at intersections?

I want to create a road-network for use with pgRouting using OpenStreetMap data. I loaded a shapefile from GeoFabrik into a Postgres table (with PostGIS enabled). However, one problem I had was that ...
6
votes
1answer
641 views

How can I make a PostGIS ST_Distance_sphere query more efficient?

Within PostgreSQL/PostGIS I have written a SQL query to get the closest distance for each element within a point coordinate table from a polyline table. The query comes back and works correctly, but ...
1
vote
1answer
276 views

Ogr2ogr - shape to postgis - is there a way to use and preserve an existing primary key?

I've been struggling with this for a while, tried a few different ideas inspired by these pages: http://www.gdal.org/ogr2ogr.html http://www.gdal.org/ogr/drv_pg.html http://www.gdal.org/ogr/...
3
votes
1answer
126 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 ...
0
votes
1answer
62 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 ...
16
votes
2answers
3k views

How to publish PostGIS Rasters in Geoserver?

What is the best solution to publish postgis rasters using Geoserver? I have spent a lot of time trying to create a Raster datasource using Image Mosaic JDBC but with no luck. Steps performed: 1. ...
17
votes
5answers
19k views

Error when trying to run Create Extension postgis

I have installed PostgreSQL 9.x and PostGIS 1.5/2.0 several times and have never had this problem. I just got a new CentOS 6.3 server up and running and and I have Postgres 9.3 functioning as ...
18
votes
1answer
4k views

Comparison of geocoding solutions (such as Gisgraphy & PostGIS 2.0) [closed]

As I am sure many of you are aware, PostGIS has finally released Version 2.0 and included within this version they have also built a geocoder (see: http://postgis.net/docs/Extras.html). Although I ...
1
vote
2answers
66 views

Can I use in a postgis db within the same table a combination of CRS?

I'm experimenting around with my postgis2.0 database. I want to create a basic table to store all my point data and in the process,I realized that the coordinates are in different CRSs. So naturally I'...
11
votes
3answers
4k views

Intersecting a raster with a polygon using PostGIS - artefact error

I'm using PostGIS2.0 to do some raster/polygon intersections. I'm having difficulty understanding which operation I should use, and what the quickest way of performing this is. My problem is as ...
1
vote
0answers
275 views

Working with ambiguous AddGeometryColumn functions

I'm using PostGIS to add geometry columns to tables, but keep receiving the error: ERROR: function addgeometrycolumn(character varying, character varying, integer, character varying, integer) is not ...