Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

548 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
10
votes
1answer
863 views

Merge adjacent polygons and return all (adjacent and not adjacent) as Multipolygon?

I have a postgres database with administrative boundaries and their geometry. Each of those boundaries have an ident number. What I want to achieve: First I want to select all rows which are ...
10
votes
0answers
683 views

Simplify the straight sections of (multi)Linestring in PostgreSQL/PostGIS leaving the curved sections untouched

I have (multi)linestrings containing upwards of 5000 points and I want to perform two simplifications possibly using Ramer Douglas Peucker or alternative methods in PostGIS. The (multi)linestrings ...
6
votes
0answers
318 views

PostGIS issue with ST_Clip?

I'm trying to clip a raster using a polygon but I end up with a result that doesn't seem right and where some raster pixels are outside the polygon used for the clip. See image below where polygon is ...
6
votes
0answers
265 views

How to remove overlapping features by percentage?

I have geometry records of type MultiLineStringZM and I am trying to INSERT new records that overlap some of the records currently in the table. If they were type MultiPolygon I could use ST_Area() as ...
6
votes
0answers
807 views

How to load the 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 ...
5
votes
0answers
120 views

PostGIS ST_Length returns 0 for geographies with high number of decimals

I'm having a weird issue when computing the distance between two geographies using ST_Length (PostgreSQL 10.4, PostGIS 2.4.4). Basically if the number of decimals is above six, ST_Length returns 0 in ...
5
votes
0answers
340 views

How to add geometries using EntityFramework 6, EF designer from database, npgsql and postgis?

I have a postgre database with postgis geometries that i would like to access using Entity framework and npgsql. I tried to go for the "EF designer from database" , but when doing so i only get the ...
5
votes
1answer
418 views

Display pictures stored as bytea in postgresql column in qgis

does someone knows a possibility to visualize in a bytea column saved photos of a postgresql database in qgis? I would like to store pictures directly into the database, not just the link/ url.
5
votes
0answers
268 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 ...
5
votes
0answers
335 views

Layers created by GeoServer do not display in Mapbender3

I have installed GeoServer 2.7.2 on my local windows 7 machine, which retrieves data from a remote PostgreSQL server. The data is represented in layers just fine! I also have installed Mapbender3 ...
5
votes
0answers
1k views

Hibernate Spatial - 'Invalid endian flag value encountered' Exception

I'm trying to run a simple query in Hibernate Spatial 4.0 on PostgreSQL 9.3. I have a number of objects in a table with latitude/longitude values, and I'm trying to query objects that fall within a ...
4
votes
0answers
88 views

QGIS projects in PostgreSQL (pyQGIS - saving proj to DB)

I'm currently working on some code to automate the creation of a QGIS project from tables in a PostgreSQL DB. For info I'm working in QGIS 3.4. I'm almost there, but I'm stuck on one thing How do I ...
4
votes
0answers
227 views

QGIS 3 Python plugin psycopg2 Postgres Notify/Listen Live Connection

I'm developing a plugin for QGIS3 for my company, where my managers can allocate tasks to other users. I have programmed two tables within the python script, witch will be only visible according to ...
4
votes
0answers
102 views

Automatically creating user CRS from postgis in QGIS

I have my custom CRS in table "spatial_ref_sys" in database. When I load layer from schema "public", QGIS create this CRS. But if layer from other schema, it's not. What I need to solve this problem?
4
votes
0answers
128 views

Problem with st_clusterWithin and conditions

I need help because I am always in front of a problem in PostgreSQL since several weeks: I have the following table (extract): Id for the identifier geom for the geometry of the polygon burndate ...
4
votes
0answers
54 views

Why do I get a memory allocation error when selecting rasters by ID in PostGIS?

The Problem I have about 256000 tiles in a table of GDEM v2 at 1 arcsecond resolution. I'm on an Ubuntu server running Postgres 9.5.5 and PostGIS 2.2. The table has a GIST index on the convex hull of ...
4
votes
0answers
222 views

Chinese postman problem

Is there any way to modify the Traveling Sales Person algorithm in pgrouting to Route inspection problem (Chinese postman problem)? I need Route inspection problem and it seems that pgrouting does not ...
4
votes
0answers
103 views

pgr_drivingdistance seems to be excluding nodes that should fall within the cost parameters set

I am getting a result that I don't understand and I am not sure if its my lack of understanding of OSM. I am using the pgrouting functions pgr_drivingdistance to get the list nodes reachable within ...
4
votes
0answers
251 views

QGIS doesn't handle PostGis connection resets?

I'm highly frustrated by the fact that if I leave the database alone for a more than a few minutes, I can no longer insert new lines. If I restart QGIS, everything goes back to normal. I believe this ...
4
votes
0answers
71 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 ...
4
votes
0answers
482 views

Performance issues Tilemill with PostgreSQL and large datasets

I have a Tilemill project the size of europe. I developed my style-sheet by using a smaller dataset (size auf Austria) and everything was perfect. But now I did apply the same styles to my big europe ...
4
votes
0answers
2k views

Clustering table on geohash index and geohash ordered table

I wonder if there could be a performance gain to re organize the rows in a table by Geohash and then CLUSTER this table on the Geohash index of the geometries. CREATE TEMPORARY TABLE tmptbl AS SELECT ...
4
votes
1answer
1k views

How to save table from PostgreSQL to MapInfo File?

I have two tables: 1 with geometry in WGS 2 with geometry in Local System I tried this: ogr2ogr -f "MapInfo File" output2.tab PG:"host=localhost user=postgres password=123456 dbname=postgis" -sql ...
4
votes
0answers
914 views

How can I properly add PostGIS data into MapGuide Maestro?

I am having trouble connecting a PostGIS database to MapGuide Maestro. Actually, I have added the PostGIS data into Maestro, but when I click to validate the layer it shows an error message: ...
3
votes
0answers
28 views

Computing driving directions in pgRouting/PostGIS?

I have calculated shortest route/path by using pg_Dijkstra algorithm in PostGIS and it is working fine. Now how to calculate driving directions from starting edge to end edge. I have gone through the ...
3
votes
1answer
72 views

How to track progress on a route?

I want to track and visualize the progress of a bus, as follows: I have the coordinates of each station as well as the coordinates of the bus, updated every 10 seconds The information is stored in ...
3
votes
1answer
45 views

osm2pgrouting creates erroneous roads

osm2pgrouting works without giving any errors but most of the roads have lost their geometry. Result is ; What I've done so far; OSM Data downloaded from http://download.geofabrik.de/ in pbf ...
3
votes
1answer
136 views

Accessing PostGIS data in QGIS?

I've created a two new PostgreSQL users; one that is supposed to have read-only privileges and another one that should have most privileges (admin-user). When I connect/log in to the new users in QGIS ...
3
votes
0answers
141 views

PostGIS geography returns area < 0.0

I am trying to convert a geometry column to a geography column and calculate the area using this query: SELECT dccode,SUM(CAST(St_Area(geom::geography,true) AS NUMERIC)) as Area FROM schema....
3
votes
0answers
125 views

Problem with insert into PostGIS view table via QGIS

I use a PostgreSQL 9.5.14 (PostGIS) and QGIS 2.18.7. The problem is that when I try to add a new object to the View via a QGIS, the following error occurs: ERROR: column "geom" is of type ...
3
votes
1answer
255 views

Mapnik + postgresql + python = Segmentation fault

I would like to render subset of OpenStreetMap features over big area into one image, no sub-tiles. System: Devuan ascii 64bit (alike Debian 9), 64GB RAM, headless (SSH only) There is what I already ...
3
votes
0answers
55 views

pgsql2shp issue when using WITH clause

I'm using pgsql2shp.exe from a Windows batch file and I get an error when using a WITH clause: "C:\Program Files\PostgreSQL\9.4\bin\pgsql2shp.exe" [ ... ] "WITH test AS (SELECT gid, the_geom FROM ...
3
votes
1answer
352 views

How do I split polygon in equal parts based on polygon area?

So, I came across this relevant question, but I think my problem is a bit different. I have two tables i.e. polygon poly and points pts in my Postgres db. I am on Windows 10 (x64) based machine and ...
3
votes
1answer
117 views

Getting the largest rectangle in the R-tree index of a PostGIS table?

Is it possible to use SQL or PL/pgsql to get the largest rectangle in the R-tree index (or other types of indices) of a table, so that it can be used as Bounding box for PostGIS table? If possible, ...
3
votes
0answers
238 views

How to plot geometry(MultiPolygon, 3044) QGIS based datatype in python

I have a table tb_regions in Postgresql having a column c_geometry which contains geometry(MultiPolygon, 3044) datatype from QGIS. I know how to retrieve data from Postgresql using pythons psycopg2 ...
3
votes
0answers
256 views

Loading one raster into QGIS from a table of rasters in PostGIS

I have a question about loading raster layers into QGIS from PostGIS, it's a an extension from the question asked [here] : Loading PostGIS raster layer into QGIS 'DB Manager', 'add to canvas' works ...
3
votes
0answers
56 views

Implementing PostgreSQL extension using PostGIS types?

I would like to create a new data type for PostgreSQL/PostGIS, but I have some difficulties. Here's what I've done so far: /* PostgreSQL */ #include <postgres.h> #include <liblwgeom.h> #...
3
votes
0answers
355 views

QGIS “Failed to add field” to PostGIS table

I have just ran into an issue with QGIS/PostGIS. Here is a summary of my problem: I have uploaded a world country boundaries shapefile as a PostGIS table to my database. I add the new PostGIS table ...
3
votes
0answers
335 views

PostGIS <-> operator does not return true distance

I want to find the closest polygon to a point. My idea was to use the <-> operator for it. According to the docs it says: For PostgreSQL below 9.5 only gives centroid distance of bounding ...
3
votes
0answers
38 views

osm2pgsql backwards compatability issue during my biannual elevation contours import process

Every two years when I upgrade to the latest debian stable I start from scratch with my elevation contours pgsql/postgis database. This year I spent an entire day trying to find some new combination ...
3
votes
0answers
76 views

Scaling postgresSQL database when searching polygons against points?

I am using our single server - CentOS 6.6 x64 16 CPU 24 GB SSD (PostgreSQL) 9.4.1 as our GIS database processing engine. This is part of the PostGIS software package installed on the server. I am ...
3
votes
0answers
508 views

postgis connection timeout in qgis

When I connect to my postgis database and edit/add features and keep the edit mode open, many times it loses the connection. I cannot save my new/edited features and I need to restart the software ...
3
votes
0answers
357 views

shp2pgsql-gui in PostgreSQL9.6 on Cent OS 7

Using Enterprise DB one click installer available at this link for Linux based systems, I have recently installed PostgreSQL 9.6 on my Cent OS 7 (x64) based machine by: su ./binary_file_name Both ...
3
votes
0answers
280 views

QGIS not loading default layer style from PostgreSQL database on startup

I have saved a vector layer style to my PostgreSQL database source. I then amended the layer_styles table record for the relevant layer by editing the styleqml field (just changed the outline_width ...
3
votes
0answers
239 views

Using PostgreSQL stored procedure with GeoServer?

I was wondering if it is possible to call PostgreSQL stored functions from GeoServer? I tried to use GeoServer's WPS to do map algebra, but found that the operations it offered was somewhat limited ...
3
votes
0answers
174 views

Export multiple Layer Definition Files (qlr)

I have a postgresql database with approximately 400 tables. I need to generate Layer definition files (qlr) in QGIS for each individual table. Making this manually would take huge amount of time, so I ...
3
votes
0answers
266 views

Interpolating point to nearest polygons to compute street width in posgis

I am trying to calculate the width of streets (line.shp) in postgis using reference point (ref_point.shp) (yellow point features) and building polygons (building.shp). The red point features are the ...
3
votes
0answers
383 views

Insert shapefile features into PostgreSQL database using java EE (pragmatically)

I am working with PostgreSQL 9.4 and Java EE. I want to insert shapefile or layer's features into PostgreSQL, is there any solution so I can insert the whole shapefile into the database? OS is Windows ...
3
votes
0answers
698 views

Can't delete postgis

geo2=# create extension postgis; ERROR: PostGIS is already installed in schema 'public', uninstall it first geo2=# create extension postgis_topology; ERROR: required extension "postgis" is not ...
3
votes
0answers
797 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 ...