Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

681 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6
votes
0answers
403 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
349 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
868 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
173 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
467 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
303 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 ...
5
votes
0answers
303 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
358 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
1answer
117 views

raster2pgsql not in C:\Program Files\PostgreSQL\12\bin

After adding both C:\Program Files\PostgreSQL\12\bin C:\Program Files\PostgreSQL\12\lib to my windows environmental variables PATH thing, I can't access raster2pgsql: 'raster2pgsql' is not ...
4
votes
1answer
144 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 ...
4
votes
2answers
83 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 ...
4
votes
0answers
370 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
0answers
111 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
169 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
61 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> #...
4
votes
0answers
66 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
336 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
132 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
76 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
507 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
942 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
38 views

Reduce QGIS project needed PostgreSQL connections

I have a QGIS project, based on data from a PostgreSQL database. The project should be used by a lot people from my company. In a performance test I noticed, that every person, who opens the project ...
3
votes
0answers
144 views

SQL 'insert into table(values)' using QGIS-modeller

How can I run a precalculated SQL query using QGIS-modeller to insert values in an existing PostgreSQL table? I know I can use import layer in DB-manager to load features in an existing table, but I'...
3
votes
1answer
33 views

Drawing line to closest point within single column

I have sets of points that define a roadway segment, and can connect them with St_MakeLine, but I have no field with which to order them, so they zig-zag. Ordering by lat/long/x/y only partially helps,...
3
votes
0answers
53 views

Function to make a trajectory more straight

I'm new at PostGIS and I have to do a project for my university course. I'm trying to analyze a trajectory of points that I downloaded from my runtastic app but it's a mess. There are some points ...
3
votes
0answers
62 views

Error trying to split a Linestring in PostGis

I'm trying to split a group of Linestrings when they cross a Polygon shape, I'm using the following query: SELECT v.*, c.name, (st_dump(st_split(v.geom, c.geom))).geom AS geom INTO table FROM ...
3
votes
1answer
251 views

Unable to read TIFF file with raster2pgsql

I am having some trouble with the raster2pgsql function (on a Windows 7 in the OSGeo4W shell). I am receiving the error "Unable to read raster file". I can import shapefiles, ok using org2org so I ...
3
votes
0answers
113 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
180 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
0answers
68 views

Using QGIS widget value-relation with PostgreSQL tables?

I am trying to implement types and subtypes to a layer of points that is stored in PostgreSQL and I wonder if it is possible to use Value Related Widget of QGIS, in PostgreSQL? I followed the ...
3
votes
0answers
610 views

PostGIS - Dynamic Server-Side Clustering based on Zoom level

I'm working on a Ruby on Rails application that is showing road signs on the map(leaflet). So, user can browse map and road signs, great. But, if there are a lot of signs and user wants to zoom out, ...
3
votes
0answers
322 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
79 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
425 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
150 views

Loading Postgres layer with Session parameter (SET) using Pyqgis

With postgreSQL 9.5 and +, there is a policy feature that handle row-level access which is great. My special case is to use this feature to control access to a multi-user postgres DB. Here is what i'...
3
votes
0answers
221 views

PostGIS ST_AsGeoJson makes geometry invalid

For the following SQL, I create a GeoJSON from a valid geometry, which makes it invalid: select st_asgeojson(geometry, 6), st_isvalid(geometry), st_isvalid(st_geomfromgeojson(st_asgeojson(geometry, 6)...
3
votes
0answers
2k views

Save Leaflet drawn features with attributes to PostGIS

Goal: In order to have a fully interactive map, I would like to be able to save some drawn features (in a second step with some attributes that may be grabbed through an input mask form or pop-up) on ...
3
votes
0answers
125 views

Loading raster to R from PostgreSQL via pgGetRast takes a lot of time

I wrote R code in R Studio to load a raster (stored in PostgreSQL) to R. The database is stored here: C:\Program Files\PostgreSQL\ The following versions are installed on the computer: R version 3.4....
3
votes
0answers
339 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
690 views

PostgreSQL/PostGIS Metadata - with QGIS

I'm stuck trying to get metadata for vector-layers into a PostgreSQL database with PostGIS-Extension. The ESRI-Environment allows to define metadata (ISO, FGDC, INSPIRE,...) and saves this information ...
3
votes
0answers
235 views

postgis raster to postgres database

hi Im trying to access my raster file in QGIS by connecting postgresql database. I can see my raster in qgis but I get this error : Geçersiz Katman: GDAL provider Cannot open GDAL dataset PG: ...
3
votes
0answers
502 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
400 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
46 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
389 views

Normalizing addresses using PostgreSQL and PostGIS?

I am trying to geocode addresses in Hawaii using the PostGres/PostGIS with the 13.1. Tiger Geocoder. In Hawaii, many street addresses take the following format: ##-####. This is problematic with the ...
3
votes
0answers
81 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
390 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
1answer
187 views

Upgrading PostGIS and PostgreSQL but running upgrade to PostgreSQL it errors?

I want to upgrade PostGIS(2.0.4 ---> 2.2.3) and PostgreSQL (9.2.4 ----> 9.6) so First I upgrade PostGIS 1、install PostGIS 2.2.3 on postgresql 9.2.4 2、alter EXTENSION postgis 2.0.4 ---&...

1
2 3 4 5
14