Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

10
votes
0answers
656 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
291 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
241 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 ...
5
votes
0answers
317 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
328 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 ...
5
votes
0answers
779 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 ...
4
votes
0answers
181 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
92 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 ...
4
votes
0answers
100 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
119 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
53 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
198 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
98 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
346 views

How to create a restorable PostGIS/PostgreSQL database backup with pg_dump?

I am unable to restore a database backup when the database contains schema's that contain geometry datatypes or postgis functions. During restore, I get the spatial_ref_sys relation not found error. ...
4
votes
0answers
242 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
66 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
692 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 ...
4
votes
0answers
254 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 ...
4
votes
0answers
472 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
0answers
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
908 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
88 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
92 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
0answers
50 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
0answers
192 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
241 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
195 views

How can I split a polygon set with a line set using PostgreSQL recursive queries?

I have a simple polygon layer GEOMETRY(POLYGON,4326) and a simple line layer GEOMETRY(LINESTRING, 4326). I want to find all split areas. I am using PostGIS 2.0. I am using following PL/pgSQL ...
3
votes
0answers
53 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
323 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
292 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
32 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
75 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
460 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
349 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
268 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
232 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
156 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
65 views

PostGIS - ST_SetValues is slow

I have large data in my C++ program (up to 3000x3000). I have connection to PSQL with PostGIS. Now I need to store my raster data (greyscale images) in DB raster. I am using ST_SetValues for this and ...
3
votes
0answers
249 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
354 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
649 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
365 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
0answers
277 views

Enumeration in QGIS

When I am using QGIS 2.8.3. I want to use a series of predetermined set of texts, such as 'wood', 'brick','concrete'. I have created an enumeration file in SQL, I do want to link that my existing ...
3
votes
0answers
494 views

Error on QGIS with PostgreSQL views?

I have a PostGIS database with various tables and views. With pgAdmin I can make requests on the tables, on the views, all is working. I add tables in QGIS 2.8, it's working, I can read, write, modify ...
3
votes
0answers
56 views

Creat point count trigger in PostgreSQL?

I wish to create a trigger in PostgreSQL for counting points in a polygon. The tweak is that the count should automatically get updated whenever the polygon is edited. Can anyone please help me on ...
3
votes
0answers
196 views

How to create raster from table containing cell values in PostGIS?

I am having a table with integer values. These values are in between 0 and 2. I want to create a raster out of that table with integers as cell values. Or else how to create polygons out of that table ...
3
votes
0answers
715 views

Postgres OSM planet reverse geocoding

i'm building function for reverse geocoding in Postgres using PostGIS function's. For now i was able to clean up all the data that was useless to me(rivers, buildings,etc.). I got 3 tables(...
3
votes
0answers
72 views

QGIS isn't displaying dates in the attribute table

I have a QGIS project that links to data in a Postgres database. For some reason, QGIS is not displaying any data in date fields. The date fields appear to be fine in Postgres and I have performed ...