PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
1answer
39 views

Visualizing results of an aggregate function query

I want to visualize the results of an aggregate function in CartoDB. For example I want to understand and visualize the top 20 owners of land by acres with the following query. As single owner often ...
4
votes
0answers
29 views

Parallel feature request connection limit

I have a large pool of feature requests that need to be performed on a PostgreSQL layer. Please assume that these requests cannot be joined into larger requests. If I spawn several threads to perform ...
3
votes
3answers
3k views

calculating percent area of intersection in where clause

I have a table of polygons (census block groups) in postgres. I want to tag each block group with the town (another polygon table) it mainly resides within. Is this possible? I'm thinking I'd need to ...
0
votes
1answer
64 views

CartoDB - PostgreSQL row_number() using PostGIS ST_Distance

I'm currently working on a query that should return a subset (i.e. "new") CartoDB table sorted by proximity to a given point. I want to display labels on the map corresponding to the closest, second ...
1
vote
0answers
28 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 http://dba....
2
votes
1answer
58 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
38 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
24 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
53 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
515 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
29 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
77 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('...
0
votes
0answers
73 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
58 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 http://packages.ubuntu.com/search?...
0
votes
1answer
82 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
300 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 ...
1
vote
0answers
18 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 mapconfig....
8
votes
1answer
191 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
142 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
50 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
87 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
94 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
195 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
48 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 out....
2
votes
1answer
56 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
397 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
63 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
36 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
119 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 ...
1
vote
2answers
99 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
89 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
53 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
102 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 ...
3
votes
0answers
59 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
44 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
58 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
582 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
52 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
41 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
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 ...
5
votes
2answers
151 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
42 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
273 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
52 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
129 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 ...
8
votes
0answers
99 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
45 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
46 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 ----------...