Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

565 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
0answers
151 views

Geoserver - How to make a psql-backed timestamp that uses timezones

I'm working on an application that extends geoserver and am frustrated as I'm trying to define a SimpleFeature that has timestamps that include timezones. I've tried declaring my timestamp field ...
2
votes
0answers
150 views

filling the attributes according to intersection of two layers (Postgresql)

How to automatically fill attributes of a point layer according to the exact position in the polygon layer? I mean how to write a command in PostgreSQL to automatically fill the attributes by the ...
2
votes
0answers
78 views

What are the problems of loading overlapping (or the same) tifs into postgis?

I am still early on the Postgis learning curve. I have an existing table in Postgres - it looks like this: create table "public"."mytable" ( "rid" serial primary key, "rast" raster, "filename" ...
2
votes
0answers
465 views

How to split linestrings where start/end points don't touch nearby lines

I am trying to figure out some basic routing with PostgreSQL/pgRouting and I'm struggling a bit. Our network of linestrings are not topologically correct, tracks that should be connected don't touch, ...
2
votes
0answers
171 views

How to Create Query Status Table Postgres

I am currently a user of a Postgres database (I am not the DBA). Within my sandbox I run several business critical scripts every day and currently track their status by first in a log text file on my ...
2
votes
0answers
111 views

Opengeosuite blank and postgresql errors

I had the unfortunate idea to upgrade opengeosuite from 4.0 to 4.1. I followed the docs found on opengeosuite site but something has gone wrong. I uninstalled opengeosuite and all connected packages ...
2
votes
0answers
93 views

Import raster to postgis and add a foreign key constraint

I use raster2pgsql to import raster files into a table with this command line: C:\Program Files\PostgreSQL\9.3\bin>raster2pgsql -s 4326 -M -F -a c:\temp\mun.tif public.basqui_raster | psql -U ...
2
votes
0answers
356 views

Geoserver WPS - Tiled Barnes Surface with geospatial index

I'm using Barnes Surface to compute a set of points from my PostgreSQL db. The problem is that my whole table is processed for each tile. I added a geospatial index on my points, how can I tell ...
2
votes
0answers
1k views

Problems moving POSTGIS database from postgresql 8.3 to 9.3 using Navicat

I am using Navicat 11 to move a POSTGIS database from 8.3 to 9.3 on Mac OS X 10.9. I have installed PostgreSQL 9.3 and PostGIS with homebrew (brew install postgresql postgis) and everything looks ok. ...
2
votes
0answers
140 views

PostGIS replace view crashes PostgreSQL server from ArcGIS script

I have the following script which when from ArcGIS (and a similar script from a QGIS plugin) will update a view. Every so often (around 1 in 5 times the script is run) it causes the PostgreSQL ...
2
votes
0answers
275 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
242 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
1k 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
199 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
618 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
244 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
245 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
221 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
235 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
421 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
607 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
520 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
24 views

Using layer style in PostgreSQL

I have a db with two schemes "public" and "versioned" (the latter generated with the fastversion plugin). In the "public" scheme I have shp with related styles_table, in "versioned" I have other shp ...
1
vote
0answers
25 views

Move points to the main façade

How can I move the points to the main façade using Postgis? I have used the following query SELECT ST_ClosestPoint(ln.geom, pt.geom) FROM "testes"."line1" AS ln WHERE ((pt.geom <-> ln.geom)...
1
vote
0answers
18 views

n:m relations of lines including directions

For trail planning and route signalization projects, I am looking for a solutions which allows me to connect "physical" trail segments (available as .gdb from authorities) with overlaying routes, ...
1
vote
1answer
26 views

Transfer spatial data between databases using pgAdmin4

I'm new to pgAdmin4 and performing tests before jumping in the deep end. I've uploaded a large dataset into a the public schema within a database named 'spatial_db'. Rather than re-uploading the data ...
1
vote
0answers
35 views

Editing PostgreSQL/PostGIS table via join in QGIS

I have a standard PostgreSQL/PostGIS table and another table which stores a geometry. They both already have a relationship with ondelete/onupdate cascade. I make a PostGIS connection in QGIS 2.18, I ...
1
vote
0answers
14 views

Extracting relevant data from CAD format into a PostgreSQL database

I have received some survey information in the format of a .DWG (AutoCAD file) and I am trying to extract the data from this file into QGIS and then into a PostgreSQL database. In the .DWG file there ...
1
vote
0answers
29 views

pgr_dijkstra invalid memory request size

I am trying to create a network of paths using pgr_dijkstra. I keep getting an invalid memory error: ERROR: invalid memory alloc request size 1080000000 CONTEXT: SQL function "pgr_dijkstra" ...
1
vote
1answer
42 views

Error while using ST_Line_Interpolate_Point: first argument isn't line

I've been running a scheduled task that contains some SQL code that has been running fine up until a few weeks ago. I don't believe any of the code had changed recently so I went through the debugger ...
1
vote
0answers
29 views

Osm2pgsql failed Cannot detect file format. Try using -r

I use Osm2pgsql 1.0 and this my action : sudo osm2pgsql --slim -c --cache 29000 --proj 3857 russia-latest.osm.pbf -H xxxx.local -d osm -U osm_user -W -P 5432 was failed after some time: ...
1
vote
1answer
45 views

ST_MakePoint outside PostGIS

To import data into a PostgreSQL/PostGIS database I need this CSV format: "1,nameOfPoint,0101000020E6100000689DA690152F114090338CFF9B0B4A40,2019-07-22 19:56:03.603+00,2019-07-22 19:56:04.243+00" ...
1
vote
1answer
99 views

Query in QGIS is significantly slower, than in pgAdmin

So, I've a table in postgresql, that is joined to other spatial table. If I've run a simple query in pgAdmin, let's say SELECT * FROM table1 WHERE "area" > '5000' and it takes less, than 1s to ...
1
vote
0answers
35 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
28 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
56 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
85 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
21 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
37 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
61 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
52 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
24 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
179 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
40 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
232 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
50 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
57 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 ...