Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

554 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
0answers
273 views

Data loss after compressing geodatabase in ArcSDE

I suffer sometimes from data loss after compressing ArcSDE geodatabase, this geodatabase contains versioned feature classes, so the compressing script reconciles and compress the geodatabase: # ...
2
votes
0answers
240 views

Styling river charts with TileMill - issue with filling a polygon made of disjoint line segments

I am trying to style inland river charts produced by the Army Corps of Engineers. I imported the vectors into Postgres, and I’m using TileMill to style the data. I am having trouble correctly filling ...
2
votes
0answers
137 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 ...
2
votes
0answers
983 views

Migrating from PostGIS to Oracle Spatial

We are now using a solution based on PostGIS extension of Postgresql database for various spatial queries. But we are now thinking about data security. This is why we have a plan to migrate existing ...
2
votes
0answers
191 views

database-backed WMS service runs as transaction

I'm trying to tame a geoserver 2.3.2 installation (backed by Postgres 9.1 and Postgis 1.5) by determining which of the many, many layers are actually being used. To this end, I've set up an audit ...
2
votes
0answers
164 views

Installing knn on PostgreSQL for windows

I am trying to install the extension KNN for clustering as an extension on my PostgreSQL database. I have downloaded the file http://www.pgxn.org/tag/analysis I found instructions on how to ...
2
votes
0answers
616 views

QGIS SPIT errors loading shapefile into PostGIS

With QGIS 1.8 on Windows 7, I just installed PostgreSQL / PostGIS and tried to load my first data into it, a shapefile using SPIT, but failed: ERROR: Geometry type (Polygon) does not match column ...
2
votes
0answers
242 views

Creating polygons for a choropleth map using PostGIS

I have a grid of points, each with a value to it as shown in the image below. The data was retrieved from PostGIS and rendered in Quantum GIS. The final result should be a polygons of a choropleth map ...
2
votes
0answers
243 views

Table for feature layer in PostGIS database cannot be published and enabled on GeoServer

I have tried to publish a table for feature layer in a PostGIS database to GeoServer 2.2.5. The layer was added and published. However, the layer has got a yellow exclamation mark in the "Enabled?" ...
2
votes
0answers
220 views

Pgrouting can't find postgresql (on Mac)

I've installed both postgreSQL 9.0 and postgreSQL 9.1 on my mac, using binaries from http://www.kyngchaos.com. I also have postgis 1.5 installed. I tried to install pgrouting, again using binaries ...
2
votes
0answers
234 views

Projecting PostGIS in OpenLayers

I have a layer (base layer) with MultiLineString geometries and now I need to project the layers (overlays) in the base layer from PostGIS using OpenLayers. However, I got an idea how to project ...
2
votes
0answers
419 views

Postgresql Query Plugins Not Returning Results in QGIS

I'm using the Fishnet function as described here to create a grid. If I use the following SQL to call the function to position a grid where I want it (I'm using NZGD2000 SRID = 2193) the query ...
2
votes
0answers
606 views

Qgis Plugin development with postgres data

can someone help me with initial developing on qgis plugin that displays postgres data table on a QTTableView or QTTableWidget? So far: I can connect to database using psycopg2 or using QtSql(tested ...
2
votes
0answers
517 views

add description to a new polygon and save -> postgres + geoserver + openlayers -> wfs-t insert

actually it is possible to draw a new polygon with openlayers and save it over WFS-T-insert (geoserver wfs-t 1.1.0) in my postgres-db: var save = new OpenLayers.Control.Button({ title: "Save Changes",...
2
votes
0answers
216 views

dijkstra_sp_directed not returning any result

I am trying to create a routing application with OSM data. What I am working with is an almost square area of about 30x30 kilometers, so nothing too big. But when I do something like SELECT ...
1
vote
0answers
18 views

Speed up the shortest distance calculation using pgRouting + PostGIS

I have to calculate the shortest path and corresponding distances for 300k+ origin-destination (OD) pairs. Using the query below it takes me around 0.3 sec to calculate one distance and therefore more ...
1
vote
0answers
23 views

Shp2pgsql-gui.exe - system error

I have got this message when I was trying to run PostGIS 2.0 shapefile and dbf loader and exporter for first time. I tryed to reinstall but it did not run after second installation either. I have ...
1
vote
1answer
50 views

Failing to JOIN ON ST_Contains

I have two tables of polygons: Table_1and Table_2. All the polygons of T1 are inside the polygons of T2. I want to SELECT the id of T1 and the name of the polygon from T2 where the polygon is located....
1
vote
0answers
78 views

PostGIS: proper way to model object that follows sections of another object's path?

First, apologies if this has been asked before. I don't really know how/what to search for, and am struggling to just put this question into words. I'm trying to model aerial utilities in a PostGIS ...
1
vote
0answers
19 views

Oracle to PostgreSQL migration with ogr2ogr only imports first geoloc column

I transfer Oracle tables to PostGIS using Oracle Wallet and ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME>...
1
vote
0answers
34 views

Combine data of OSM roads into longer lines using PostGIS

For my vector map I'd love to display highways at a quite early zoom level. The problem is that this data would be quite big, so I thought I simply ST_Simplify it. The problem though is that the OSM ...
1
vote
0answers
30 views

Whole schema migration from Oracle Spatial to PostGIS import using ogr2ogr

I can migrate Oracle SQL table to PostgreSQL using ogr2ogr using: ogr2ogr -progress -f "PostgreSQL" -nln schema.table -skipfailures -overwrite -lco FID=ID_COLUMN -lco GEOMETRY_NAME=geom PG:"host=...
1
vote
0answers
39 views

Storing raster imagery in PostgreSQL

I have a number of raster files saved locally which I use for basemapping in QGIS. I have built virtual raster fields to load these in one go but I'm looking to store them within my PostgreSQL ...
1
vote
0answers
23 views

Can't DropTopology due to event trigger

I have a postgres database with one table for each US state. I am trying to make a program which iterates through each of the tables and manipulates the geometry. To do this, at the beginning of the ...
1
vote
0answers
91 views

Error when importing merged osm.pbf files into nominatim's PostgreSQL database

Im using Nominatim 3.3 (https://github.com/mediagis/nominatim-docker/tree/master/3.3) within docker. I already imported succesfully 2 regions (not together, my PostgreSQL database contained one region ...
1
vote
0answers
35 views

Vector tile generation

I have a full planet imported in PostgreSQL with imposm3. What is the best way to generate vector tiles (MVT in .mbtiles) from PBF/PostgreSQL with maximum hardware utilization (distributed generation ...
1
vote
2answers
160 views

psycopg2.ProgrammingError: function does not exist

I am using Python code cur.callproc('function_a',[parameter1, parameter2]) for executing one function and this code is correct when I use a user having full DB permissions. But when I use a private ...
1
vote
0answers
41 views

Simple clustering example

I'm trying to use postgis ST_withincluster function. But my knowledge over SQL seems a bit too fresh at the moment and I can't figure out by myself how I should proceed by reading posts like this one: ...
1
vote
0answers
48 views

How to create a function for getting a largest polygon from 2 polygons using case statement in PostgreSQL?

CREATE OR REPLACE FUNCTION public.largest_polygon1( geom1 geometry, geom2 geometry) RETURNS geometry LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ begin Return Case ...
1
vote
0answers
32 views

Using CSW for GeoServer PostGIS database

I am quite new to GeoServer and general OGC fundamentals. I have GeoServer set up and I set up a PostGIS data store which links to a database I have already been able to make successive calls using ...
1
vote
0answers
65 views

Geometry type (Point) does not match column type (LineString)

I'm using PostGIS 2.5.2 on PostgreSQL 10, and I'm trying to build a function which would split a LineString into portions, depending on points positionned close to it. I'm getting a Geometry type (...
1
vote
0answers
33 views

How to speed up PostGIS intersects query?

I have a table which contains 5,00,000 records. I want to fetch all the rows which intersects with the given bounding box. My query is: select * from data_master b where b.geom && '...
1
vote
0answers
77 views

Map Matching between two geometry (line string)

I have two tables in PostGIS, both having line string as the geometry type. I wanted to extract a similar or exact matching street. Can you please suggest query or algorithm for it? Table 1 column ...
1
vote
1answer
55 views

Simple point in polygon query at PostGIS & PostgreSQL

Using PostGIS & PostgreSQL (Amazon RDS instance), I need to create a (quite large) table with points (just x, y coordinates) Then what I need to do is very simple point-in-polygon queries. Points ...
1
vote
0answers
36 views

Error with generate_series

I`d like to create a Hex Grid using Hex grid algorithm for PostGIS (https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093) DO $$ DECLARE _curs CURSOR FOR SELECT geom3857 ...
1
vote
0answers
28 views

Data type for mixed representation

The following is a JSON representation of an large array of planar spatial surfaces. {"type":"Feature", "geometry":{ "type":"MultiPolygon", "coordinates": [[[[17.7529,-31.146],[17....
1
vote
0answers
28 views

How to join multiple nearest neighbouring nodes using postgresql or python?

I have many short paths SHP file which look like a longer dashed path. Is there an efficient way to connect these paths to one continuous path? If possible, I want to avoid a hundred times manually ...
1
vote
0answers
59 views

ERROR: function st_orientedenvelope(unknown) does not exist

I tried the example for ST_OrientedEnvelope given in PostGIS website but that failed. This function is failing for my table geometry column also. SELECT ST_AsText(ST_OrientedEnvelope('MULTIPOINT ((0 ...
1
vote
0answers
45 views

PostGIS: Is it interesting to create an index on geom column and an alpha numeric field?

I would like to know if it's interesting to create an index on an other column than the geom one. In some cases, I would like to do spatial analysis. If not, I would like to analyse only attributes. ...
1
vote
0answers
18 views

Editing attributes in ArcMap causes conversion of arcs to densified lines unintentionally

I am using postgresql GDB for storing and managing polyline feature classes through arcmap but when I create arc feature along with filling any attributes the arc is being converted to a densified ...
1
vote
0answers
240 views

Cache lookup failed for function 1 while activating PostGIS

I'm using PostgreSQL 10.5 on a remote server (Debian). I created a database inside then I attempted to enable PostGIS but it wasn't successful. I had this error : cache lookup failed for function 1
1
vote
0answers
65 views

Extracting all cities from a state using PostGIS

I have written a PostGIS query on a database with OSM data of the state of Baden-Württemberg. SELECT * FROM public.planet_osm_polygon WHERE boundary = 'administrative' AND admin_level = '8'; So ...
1
vote
0answers
37 views

Shortest path with certain points

I have a warehouse that has many storage yards. I have an order. It has items list and I have to collect them from storage yards. Storage yards are not equal each other. Their length are different ...
1
vote
0answers
92 views

ST_AsMVT single layer with multiple Id's

I'm trying to create mvt using postgis by following this tutorial If understand correctly ST_AsMVT is used to create a layer and generated layer will have id. same id can be used to apply styles. ...
1
vote
0answers
29 views

What is the difference between pgr_createtopology and pgr_createverticestable?

I'm using pgRouting and I'm going through the manual (Release 2.1.0) and in chapter five, it says: • pgr_createTopology - to create a topology based on the geometry. • pgr_createVerticesTable - to ...
1
vote
0answers
70 views

line_interpolate_point: 3rd arg isn't within [0,1] strange behavour

I have a strange berhaviour in this postGIS query: SELECT st_length(st_linemerge(v.geom)), c.prog_ini, c.prog_fin, c.prog_ini/st_length(st_linemerge(v.geom)) as ini, c.prog_fin/st_length(...
1
vote
0answers
35 views

QGIS 3.X CAD tools not working with snapping on PostgreSQL layers?

If I create a scratch layer in EPSG 32720 CAD Tools are working fine with snapping options. The vertex are highlighted etc. etc. If I use a PostgreSQL layer, same EPSG, the snaping is working, but ...
1
vote
0answers
89 views

QGIS Plugin: display dialog for entering username and password to connect to the database

I do not want the password and username to be saved in the QGIS project. Instead of standard QGis dialog(QgsCredentialDialog) I want to display "my dialog" for entering username and password. On the "...
1
vote
0answers
37 views

TableToTable_conversion using PostgreSQL and ArcPy

Currently, I am trying to convert a geodb to a table in my PostgreSQL db with arcpy as such. I've tried both with the parcels table being created and the parcels table not being created arcpy....
1
vote
0answers
15 views

How to create a sub region shapefile from MasterMaps on QGIS?

I have the OS Mastermaps on a PostGIS database and this is connected to QGIS. Mastermaps is visualised well on QGIS with all the layer styles. A region is clipped from PostGIS and created a new ...