PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
0answers
27 views

GIS Workorder System [closed]

First off, these questions are relevant: http://dba.stackexchange.com/questions/7900/pros-and-cons-of-having-one-table-per-entity-vs-having-a-shared-table-with-an-e/7907#7907 ...
2
votes
1answer
54 views

Removing duplicate labels from different tables

I am making a map using data stored in PostGIS. I am serving multiple layers in the map (POIs, Cartographic, Roads etc.) and when I label these layers I have repeating labels, this is because some ...
1
vote
1answer
31 views

Find Delta only records in ArcGIS (SDE)

We have spatial layers in ArcSDE with objectid as unique value, I've taken this data to another platform & migrated data to cloud. Now, after months, back in ArcSDE and want to migrate new data ...
0
votes
0answers
22 views

Can I used a share drive as a PostgreSQL server?

I am currently using my C drive to practice and I have a shared drive with my co-workers where we store shapefiles. I wanted to start another server in our shared area to have us all connect the files ...
0
votes
1answer
40 views

PostGIS Query Geometry Field Truncated

Some of the geometry fields that are returned from my PostGIS queries are truncated. When I first found this error any geometry fields with more than a few thousand characters would not show up in the ...
3
votes
0answers
474 views

How to load the default layer style from Postgresql with Python?

I would like to write a python macro that upon opening my QGIS project file will reload the default style for each layer from the postgres 'layer_style' table. I have had a look at the documentation ...
1
vote
0answers
27 views

pgrouting pointAtoB function

Has anyone successfully implemented pgr_fromAtoB function? Sometimes I get 0 results from points in the same line string, sometimes I do get results, How can accuracy be improved?, or maybe do I ...
0
votes
1answer
47 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 ...
0
votes
0answers
47 views

How to correct this error Caused by: java.lang.ArithmeticException: / by zero in geoserver

My environment is: geoserver 2.8.1 image mosaic JDBC java 1.7.0_79 centos 6.7 postgresql 9.4.5 postgis 2.1.8 I used ImageMosaicJDBC to publish my tiff image in geoserver. But, I get the error ...
0
votes
1answer
55 views

Is there some method on Ubuntu to request newest postgresql postgis package rather than specific version?

There is postgresql-9.3-postgis-2.1 and there is postgresql-9.4-postgis-2.1. Unfortunately 9.3 is available only on trusty and 9.4 is not available on trusty (see ...
0
votes
1answer
56 views

Can updates be made to “ogr_fdw” foreign tables in PostgreSQL?

I am accessing a Microsoft SQL Server table (w/ SQL Server a geometry column) via an ogr_fdw foreign table. SELECT statements work great, including the PostGIS functions that I've tried. Are UPDATES ...
1
vote
0answers
253 views

Geoserver ( Java.exe ) cause server to run 100%

Our server runs at 100% CPU when using map services build on geoserver + postgresql I can not find anything in geoserver logs. It is Java.exe takes 100% CPU and because of that server can not do ...
0
votes
0answers
16 views

Group similar features in Geoserver

I have created a view in PostGIS with 1:M relationship. Now I want to display that view in Geoserver. But the problem is that my each feature is represented multiple time. How can I show the ...
3
votes
3answers
1k views

How to route between lat/lon pairs in pgRouting?

I have set up Postgresql, PostGIS and pgrouting on Ubuntu. I have imported an osm for wales using the following command: ./osm2pgrouting -file wales.osm \ -conf ...
8
votes
1answer
178 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
0answers
75 views

PostGIS error while adding feature: no result buffer

Can anyone explain to me the following error and possible fixes? I am using QGIS 2.12.3 to connect to a PostgreSQL 9.5 database. What confuses me is that I was able to add a feature and save, but ...
4
votes
1answer
42 views

Separate isolated polygons using PostGIS

I am new to PostgreSQL and have been trying to separate polygon features in a PostGIS Table into 2 different Tables based on a condition. All polygons which touch or overlap with one another needs to ...
3
votes
0answers
66 views

How To save Geo-Tiff image in PostgresSQL?

I have a Geotiff image, which I want to load into a PostgreSQL database. Currently I am using the command line code for saving this image to my database : raster2pgsql -I -C -e -Y -F -s 26986 -t ...
2
votes
2answers
78 views

PostGIS raster MapAlgebra callback optimization

I'm trying to speed up an ST_MapAlgebra callback function that essentially takes a raster with four bands, checks each band for values less than a given number, and where the check is true, return the ...
2
votes
1answer
188 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 ...
0
votes
0answers
37 views

Export image from PL/R on postgres

I'm trying to export an TIFF image from a PL/R Function in my PostgreSQL database. The output of the image appear be corrupted, because when I'll open the image, is showing the message: "Loading ...
2
votes
1answer
44 views

Renaming a table in the QGIS DB Manager

When creating a PostGIS table (e.g. 'lakes') in the DB Manger a primary key ('lakes_pkey'), a index ('sidx_lakes_geom) and a sequence ('lakes_gid_seq') are also created. But when renaming the table ...
3
votes
2answers
344 views

adding geometries to a source, target table for multiple rows in pgrouting

I am having an issue trying to perform the pgr_dijkstra algorithm and getting back a line in my table for multiple rows. I have a table that has a columns of source, target node id's but I cannot ...
2
votes
3answers
52 views

Update existing Postgres table from KML?

This is related to this question, but different because it's about PostGis, not Django. I'm using Postgres 9.4 with PostGIS extensions. I have a KML file, and I want to use it to update the ...
5
votes
3answers
1k views

Debugging ogr2ogr “AddGeometryColumn failed” and “Terminating translation prematurely after failed translation of layer”

As noted in the title, I'm getting the following error: ERROR 1: AddGeometryColumn failed for layer pretty_polys, layer creation has failed. ERROR 1: Terminating translation prematurely after failed ...
0
votes
0answers
27 views

Mapnik render from a postgres cluster on an external drive

Because of limited space on my laptop I had to set up my europe-latest.osm.pbf on an external drive. Keeping the flat-nodes on my laptop reduces the processing time. The main time is spent on creating ...
0
votes
2answers
95 views

Modifying “pg_hba.conf” file to listen a dynamic address

this is the sequel of "Modifying "pg_hba.conf" file to listen local TCP/IP conection" I manage to modify pg_hba.conf to listen other PCs and allow them through the firewall, but my problem now is ...
0
votes
2answers
54 views

Reduce query time when using both MySQL and PostgreSQL with PostGIS

I am doing a tracking query. The device is sending its location data, timestamp to store in MySQL Database. I want to show the query result of every point with its nearest POI. The POI database is ...
4
votes
1answer
54 views

Datum and transformation error using ST_Transform in PostGIS, going from NAD83 to NAD27

I created a function in PostgreSQL to transform input coordinates using UTM Zone 12/13, with Datums NAD83 or NAD27, to the WYLAM projection. I had to manually create the entry for the proj4text ...
1
vote
1answer
36 views

Referencing variables inside ST_Distance_Sphere function

I trying to create an UPDATE function that will ultimately return straight-line distance to the nearest node on an OSM network (which will be later added to pgrouting distances I have already ...
1
vote
1answer
80 views

OSM2PO Postgres to graph for A*

I have created a directed graph from the nodes stored within my Postgres db but the resulting graph doesn't seem to be fully connected and as a result my shortest path algorithm is not able to find a ...
2
votes
0answers
35 views

ogr2ogr imports GML data (not from OSM) to Postgis but without the other_tags “hstore”-column

The problem I'm encountering some issue trying to use ogr2ogr to import some GML input data (not OSM) into Postgres. I'm trying to use COLUMN_TYPES=other_tags=hstore to store all the fields not ...
3
votes
1answer
36 views

Install PostGIS from source for specific PostgreSQL version [closed]

I have two versions of PostgreSQL on my machine (Ubuntu 12.04) one is 9.1 and the other one is 9.4. I want to install from source (I have my reasons) postgis 2.1.2 over PostgreSQL 9.4, but when I ran ...
2
votes
1answer
49 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?
6
votes
2answers
444 views

How to use interacting PostgreSQL/PostGIS triggers in QGIS to create multiple labels on polygons?

EDIT: The trigger functions posted are well done but there are still a few issues. I've uploaded two screen recordings: http://workupload.com/file/ygRnYtp9 http://workupload.com/file/OBY7i9Fh I ...
2
votes
1answer
35 views

How to update a timestamp in SQL?

I've explored this forum fairly extensively trying to get answers as to why my SQL isn't working. I need help with the timestamp on a torque map. I've used the following formula, obviously changing ...
2
votes
1answer
34 views

org2ogr PostgreSQL to GPX

I already have found how to import GPX files into PostgreSQL using gdal/org2org. Now I am wondering of dumping into GPX files the results of my modification on dataset stored into PostgreSQL. But I ...
2
votes
0answers
126 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 ...
5
votes
2answers
125 views

How to use ST_Difference to create linestrings from non-intersecting polygon boundaries?

There are two tables with polygon features in my PostgreSQL database. I want to create linestring features where the boundries of the polygons are not intersecting. CREATE TABLE schema.poly_a ( gid ...
1
vote
0answers
33 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
5answers
216 views

Create Statewide Hydrologic Soil Group Map

This may be better for Engineering? Not sure. Background: Previously I have downloaded SSURGO soil data for the state of Pennsylvania by downloading each county and merging the shapefiles. I then ...
2
votes
0answers
34 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 ...
3
votes
1answer
113 views

Strange behavior of of ST_Intersects in PostGIS

I'm quite new to GIS and PostGIS and, probably, this question has been already answered multiple times, but unfortunately I was not able to find an answer. I have few polygons and I want to find ...
7
votes
0answers
67 views

Accessing the internal data structure storing Postgis spatial index (PostGres GiST)

I am curious about how the internal data structures and storage mechanism of the spatial GiST index in Postgis. In Oracle, I used to like to show how the SDO indexes were "just another table", and how ...
2
votes
0answers
31 views

Create network graph from contiguous polygons in KML, or PostGIS?

I am using Postgres 9.5 and PostGIS. I have a table with a geometry field. This table has about 200 rows, in each of which the geometry field contains a Polygon. I would now like to derive an ...
1
vote
1answer
34 views

Syntax error in a query with ST_AsText and ST_CENTROID

I have a table in a PostGIS database where with this query : SELECT ST_CENTROID(pol8.geom) AS geom FROM polygon8 AS pol8; I got this: geom ...
2
votes
1answer
21 views

Multi-string data-set without z layer, applying ordinal values as a workaround

I'm new to GIS and have been learning a lot over the last few weeks, however, I've finally come to a point where I could use some help. I'm working with the pdx_streets data-set from ...
2
votes
0answers
44 views

How to clean the postgres instance while setting cartoDB locally in ubuntu

I am following https://github.com/danseely/cartodb-install/blob/master/DEV-INSTALLATION.md for setting cartoDB locally in ubuntu12.04. While installing cartoDB-postgresql, the second install check ...
0
votes
0answers
28 views

Installing Osm2Pgrouting on debian?

I'm new to Postgis. I'm trying to install osm2pgrouting on debian, but when I run cmake -H. -Bbuild I get an error : Could not find PostgreSQL (missing: PostgreSQL_TYPE_INCLUDE_DIR) (found version ...
1
vote
1answer
30 views

Issue with GeoJson data format fetching from PostgresQL

I am trying to display the GIS data on the Google map using PostgreSQL. I have few Shapefiles, I imported those shapefile into PostgreSQL. Now I am trying to get this data using this query. After ...