Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

552 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
10
votes
0answers
701 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
327 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
287 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
1answer
442 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.
6
votes
0answers
812 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
132 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
359 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
0answers
279 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
346 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
108 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
265 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
1answer
155 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 ...
4
votes
0answers
167 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....
4
votes
1answer
397 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 ...
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
137 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
58 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
239 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
112 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
255 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
73 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
486 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
919 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
61 views

PostGIS JOIN table ON ST_Intersects

The following query and subquery help me finding a parcell using a GPS coordinate: SELECT row_to_json(fc) FROM( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features ...
3
votes
0answers
107 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
78 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
47 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
0answers
157 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
290 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
61 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
125 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
270 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
267 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
57 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
378 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
357 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
41 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
77 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
347 views

'ST_Subdivide' function (3.5.0+ required) GEOS

Im running a query with ST_SubDivide() and receiving the error bellow. ERROR: The GEOS version this PostGIS binary was compiled against (34) doesn't support 'ST_Subdivide' function (3.5.0+ required) ...
3
votes
0answers
531 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
365 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
292 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
250 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
188 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
279 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
403 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
729 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 ...