Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

556 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
714 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
823 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 ...
3
votes
0answers
399 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
296 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
518 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
1answer
859 views

Calculating total line lengths within polygon?

I have basically the same conundrum as Calculating line segment lengths within polygon using ArcGIS Desktop? but I'm working in PostGIS, not ArcGIS. Any pointers? This query seems to like it should ...
3
votes
0answers
204 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
733 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
74 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 ...
3
votes
0answers
658 views

Can't see Postgis layers added to QGIS

I'm trying to transition from an old Postgres/Postgis database to a new one. I'm working in a fresh install of Win 7 64 bit. I installed Postgresql 9.3 32 bit, then installed PostGIS 2.1.3. I created ...
3
votes
0answers
270 views

Raster slightly displaced after exporting from PostGIS database

With the following queries I've exported a raster (GTiff) from my postgis database: 1.Creating a large object: WITH buffer AS ( SELECT id, ST_Buffer(geom,50) AS geom FROM ...
3
votes
0answers
775 views

How do I catch PostGIS/PostgreSQL RAISE messages in QGIS?

I have a trigger set up in PostGIS/PostgreSQL which will show a RAISE WARNING when an inserted or updated polygon is within a certain distance of some predefined planning constraints. I want this ...
3
votes
0answers
1k views

Smoothing Lines in PostGIS

I want to smooth Lines in postgis, to be more specific, I want to smooth road data from OpenStreetMap. Basicly I thought of something like this scenario. This would be a possible road segment. I ...
3
votes
0answers
855 views

Adding osm data to Postgres via Osm2pgsql : duplicate key value violates unique constraint

I'm trying to import osm global world data into a database created in postgreSQL but after a while of processing, I've got this error : Reading in file: E:\planet-latest.osm\planet-latest.osm ...
3
votes
0answers
85 views

How to load a raster from in-memory bitmap (not from file) into PostgreSQL

I have a bitmap in memory that I would like to load as a raster to PostgreSQL. An unfavorable option is saving the bitmap to file and then use raster2pgsql, but as we have several cameras that ...
3
votes
0answers
1k views

Select features within a country or bounding box effectively using PostGIS

I imported the planet OSM file into a PostgreSQL 9.2/PostGIS 1.5 server using osm2pgsql. I now want to select certain map features which lie within the bounding box formed by a countries borders, e.g. ...
3
votes
0answers
1k views

Is there a way to start the route on pgrouting from a coordinate that is not a node?

After execute all steps from this pgRouting tutorial, now I have a map that creates and displays routes, but as the routing methods rely on parameters representing vertex Ids that was created using ...
3
votes
1answer
6k views

How to write a point-in-polygon query for Postgis?

I'm trying to write a query in Postgis which will return all rows where a dynamically changing longitude/latitude point resides within the geometric polygon of that row (in the geom column). This is ...
2
votes
0answers
21 views

Restoring a PostgreSQL/PostGIS backup into a new database with fresh extensions

I'm working on a migration from a very old server (Ubuntu 10.04/Postgres 8.x, etc) to a new instance (18.04) with up-to-date versions of everything in our web stack. Our PostgreSQL database uses a ...
2
votes
0answers
43 views

Changed attribute order of PostGIS layer in QGIS

I'm working in a PostgreSQL table (PostGIS) that I loaded into QGIS. This layer has been created by a SQL Statement in pgAdmin. I noticed that the order of attributes shown in the attribute table ...
2
votes
0answers
42 views

Finding Intersection Points in Stream Network using QGIS?

I have a stream network and I want to find the red points as shown below. I have tried using Q-chainage and Line Intersection feature in QGIS but it gives more than expected number of points. The red ...
2
votes
0answers
33 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 ...
2
votes
0answers
37 views

Creating GeoJSON REST service

I'm trying to create a GeoJSON REST service from a PostgreSQL connection. First I fetch GeoJSON as a string from PostgreSQL: import java.util.*; import java.sql.Connection; import java.sql....
2
votes
0answers
28 views

Point cloud query is very slow and not using parrallel scan or more memory

The following PostGIS query is running very slow (> 1 day for about 1 million records): UPDATE test.table1 SET area= ST_Area(PC_EnvelopeGeometry(pa)) WHERE area is null or area <> ...
2
votes
0answers
38 views

Accessing PostGIS layers as non-data owner via QGIS?

I'm trying to create a database user (postgres) to access PostGIS data via QGIS. I've applied the following commands: GRANT ALL ON SCHEMA PUBLIC TO ene; GRANT SELECT ON ALL TABLES IN SCHEMA public ...
2
votes
0answers
70 views

Trigger PostgreSQL

I have a pb to make a trigger. I have 3 tables and a georeferenced layer: The common table contains: PKcom, INSEE The cadastre table contains: PKcadastre, Fkbat, section, parcel The building table ...
2
votes
0answers
46 views

Creating closest line along the point''s azimuth using PostgreSQL

I have a point table which contains start point and end point: When I am writing this query in PostgreSQL to create line it gives me this output. select distinct on (a.id) a.id, min(...
2
votes
0answers
48 views

Per row commit when editing PostGIS data with QGIS, with PostGIS triggers checking inserted data

I have a PostGIS trigger assuring that records with a Point-geometry inserted in a table fall within Polygons in another table. Simple example: -- Trigger function CREATE OR REPLACE FUNCTION ...
2
votes
0answers
21 views

What is stored in Options|Advanced|PostgreSQL|connections|dbname|keys group in QGIS 3?

Since QGIS 3 in the Options|Advanced|PostgreSQL path referred to in the title there is a new group 'keys' as can be seen in this screenshot: In old QGIS 2.X: What kind of information is stored in ...
2
votes
0answers
186 views

GDAL PostgreSQL shapefile import “Unable to find driver 'PostgreSQL”

I have seen a number of posts similar to this but with no resolution. I am experimenting with GDAL commands and am fairly new to it however when using a PostgreSQL database connection I am getting ...
2
votes
0answers
250 views

Implicitly convert Geometry to WKT string in PostGIS

Suppose I have a table defined as follows: CREATE TABLE gtest (name varchar, geom geometry); To insert, I am able to simply do this: INSERT INTO gtest VALUES ( 'Polygon', 'SRID=4326;POLYGON(...
2
votes
0answers
302 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, ...
2
votes
0answers
171 views

PostGIS queries result in “Symbol not found: __ZN5boost6system15system_categoryEv”

I seem to have lost the ability to use basic postgis queries. Normal postgres queries are unaffected. Using Mac (High Sierra), using postgres 10.5 / postgis 2.4 (probably) The same error happens ...
2
votes
0answers
50 views

PostgreSQL/PostGIS: Adding attributes to a table that I can use to query with

Currently I have a PostGIS database that is taking in shapefiles. However, these shapefiles are converted into tables when they are imported into the database. Each shapefile deals with a farm name, ...
2
votes
0answers
555 views

PostgreSQL and PostGIS install on RHEL server

I am trying to install both PostgreSQL and the PostGIS extension onto my RedHat Linux server. I have been able to install PostgreSQL with the yum installation however I cannot install PostGIS this way ...
2
votes
0answers
79 views

Geography point and integer in multicolumn PostgreSQL index

I want to create a multicolumn spatial index on a geography point and integer in order to do efficient spatial queries. The integer is a user_id and queries are always limited to a single user. Is a ...
2
votes
1answer
621 views

Cache lookup failed for function 1 while enabling postgis on postgres 10

I am trying to enable PostGIS on a PostgreSQL 10 database on Windows. Earlier it used to work fine, but now I am getting an error 'cache lookup failed for function 1' on enabling the extension. I am ...
2
votes
0answers
180 views

osm2pgsql --append super slow (node 2k/s vs 1500k/s for --create)

I'm working with multiple pbf files (one per administrative region). I thought I would import them one by one in postgis, with: osm2pgsql --proj 4326 --hstore-all --database gis first.pbf --style ...
2
votes
0answers
43 views

PostGIS/QGIS visualization with strange characters in attribute table

I loaded a shapefile into a PostGIS database. When I visualized it in QGIS I noticed that in the attribute table, some fields with integer data type, are displayed in a strange form. However when I ...
2
votes
0answers
196 views

Unable to show geometry in virtual layer qgis

I'm trying to create a virtual layer starting from a municipality layer and from a table of attributes, both stored in a Postgresql DB. So I've done a join like: *SELECT munic, country_of_origin, ...
2
votes
0answers
291 views

Improve the speed for ST_DWithin

I need to get the outer polygon of the LSOAs within 10 miles of each postcode centroid. There are 180k LSOA polygons in table1 and 2.6 million postcode centroid points in table2. I have created 2 ...
2
votes
0answers
74 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'...
2
votes
0answers
167 views

Connecting to PostgreSQL using QGIS using a VirtualMachine?

I installed Postgresql in Windows server 2012 Essential on Virtual Machine. Everything works ok , but when i try to use QGIS Desktop which is on desktop installed to access to Postgresql-data , the ...
2
votes
0answers
38 views

Is there an equivalent “why-upgrade PostgreSQL” tool for PostGIS?

I rarely keep up with the PostgreSQL and PostGIS releases and it can a challenge deciding on when to do so for the major/minor releases. The why-upgrade site (http://why-upgrade.depesz.com/show?from=...
2
votes
0answers
162 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)...
2
votes
0answers
114 views

PostGis st_dwithin super slow when inserting

I am trying to insert the newly obtained data from st_dwithin into a new table. However, it has been running since 1 day and I still can't get any data into the new table. Can i know what is the ...
2
votes
0answers
93 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....
2
votes
0answers
215 views

ST_Voronoi is creating invalid geometries

I have a workflow where I am creating Voronoi polygons from a series of points. However since the polygons on the exterior extend really far out I have clipped them and then joined them back to points ...
2
votes
0answers
250 views

geopandas dissolve versus postGIS postgresql aggregated groupby dissolve

I have a geodataframe for which I want to dissolve the geometry based on an attribute (fao_id). In geopandas this is easy as gdf.dissolve(by="fao_id", aggfunc="first") but unfortunately this fails due ...