PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
1answer
57 views

Cannot generate nationscript.bat - Tiger Geocoder

I have been attempting to install tiger - geocoder in a postgres database. After muddling through examples and resources online for days I have been able to conjure up this much of the workflow. I ...
2
votes
2answers
93 views

Why does FeatureClassToFeatureClass work in ArcMap but not in Python script?

When trying to copy a feature class from an SDE connection file to a scratch GDB, the FeatureClassToFeatureClass_conversion works in the ArcMap Python window, but I can't get it to work in a Python ...
4
votes
1answer
111 views

How to conveniently generate a Google Maps link from a PostGIS geometry?

When working with a spatial database, it is often convenient to make a sanity check for a location of a point by viewing it on Google Maps (or any other online mapping service). Copying the lat/lon ...
4
votes
2answers
130 views

Filter data with PostgreSQL or geoserver?

I'm working on a PostgreSQL database with PostGIS. I need to filter an 8 million records table in order to publish a view through geoserver. I know two ways to do that, one is that I create view in ...
0
votes
0answers
69 views

Postgres PostGIS geocode_intersection()- How to handle exception?

I have a table in Postgres with a set of records that are street names which needs to be geocoded to get an intersection point.Some of the records would be bad input and when using the ...
1
vote
1answer
49 views

raster2pgsql size problem

I am about to import a raster (.tif with 50mb, size 3301x21676, pixel size 10x10) into postgres with raster2pgsql. With small extracts of the image it works but ... raster2pgsql [myfilename].tif -F ...
3
votes
2answers
339 views

What does the values in column clazz (osm2po) mean?

I have a given table in postgreSQL with OSM data which is used for routing purposes in Java with PGRouting. There exist two columns which I don't understand. They belong to osm2po. They are labeled ...
2
votes
2answers
75 views

Postgis Functional Index on st_pointonsurface() Ignored

If I define the following table... CREATE TABLE tbl_vast_importance( id bigserial PRIMARY KEY, geom geometry(MultiPolygon,4326) ); CREATE INDEX ON tbl_vast_importance USING GIST (geom); CREATE ...
3
votes
1answer
54 views

Postgis Query Construction - postgresql

I'm a newbie and I need a little bit help with postgis query construction. My situation looks like this. I have a table in postgresql with ways converted from bicycle routes (take from Open Street ...
0
votes
0answers
17 views

How can I speed up Cartaro page load times running on XAMPP Localhost?

I successfully installed Cartaro using OpenGeo Suite 4.8. Accessing GeoServer via localhost:8080/geoserver works efficiently. Access GeoServer from Cartaro localhost is extremely slow. Access to ...
2
votes
1answer
49 views

PostGIS function ST_SymDifference between two segment groups produces unexpected results

I try to calculate the symmetric difference for a set of segment groups A and B with precision of 1E-5 (ST_SnapedToGrid(geom,1E-5). I've formulated the an ideal test case for a H based shape segment ...
1
vote
1answer
37 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
27 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
58 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 ...
2
votes
1answer
57 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
33 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
23 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
45 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
487 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
59 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
55 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
56 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
60 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
264 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
17 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
180 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
90 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
45 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
71 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
83 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
189 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
40 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
48 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
367 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
54 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
33 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
103 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
71 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
66 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
39 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
94 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
42 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
37 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
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?
6
votes
2answers
494 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 ...