The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

567 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
876 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
424 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
317 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
527 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
898 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
736 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
76 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
667 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
276 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
782 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
863 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
51 views

Cannot edit QGIS project in QField (save to PostgreSQL db)

I struggle to edit my project in QField on a Samsung Galaxy Tab Active 2. The general idea would be to create and edit points in the field and save them in a PostgreSQL db (offline and online). What ...
2
votes
0answers
39 views

How to make Dijkstra faster?

I would like to compute shortest path between 2,5 millions pairs. I created table with id, origin, the nearest node of origin, destination, the nearest node of destination. I also created index on ...
2
votes
0answers
33 views

Storing distance between two points in KM

I am very new to PostgreSQL and PostGIS. I am filtering a collection of messages based on their distance from a point which works great, but I would like to be able to store the distance between the ...
2
votes
1answer
331 views

Installing PostGIS on PostgreSQL 12

I installed PostgreSQL 12 using Windows Installer. After the installation, there´s no option to install postgis extension. I tried to add postgis support in a database using CREATING EXTENSION and I ...
2
votes
0answers
38 views

Issues synchronizing QField into PostGIS

I'm having issues synchronizing my QField project into a PostGIS database. Nothing is synchronized, and I have the following error in the PostgreSQL logs: 2019-08-29 13:06:34 HST INSTRUCTION : ...
2
votes
1answer
62 views

Connecting to PostgreSQL database from another machine using QGIS?

I have a database with name "Test" in PostgreSQL 11 in my machine, I can connect to this database from my machine using QGIS with host localhost, but when I tried to connect it from a different ...
2
votes
0answers
25 views

Querying / seting up PostGIS (raster) table to efficiently get all bands at once?

I have created a PostGIS raster table from raster2psql. I have use cases where I might have bands sizes of 114 to 670 bands inside a raster (The number is consistent across a given dataset.) My ...
2
votes
0answers
42 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
49 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
37 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
42 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
34 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
73 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
54 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
49 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
228 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
299 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
360 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
176 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
53 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
651 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
89 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
0answers
97 views

Is there a function to obfuscate a PostGIS point or geometry?

I am looking for a function, perhaps a Perlin noise implementation or something similar, for PostgreSQL/PostGIS. Does such a thing exist in core PostGIS? If not, does such a function exist an ...
2
votes
1answer
650 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
204 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
44 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
216 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
315 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
83 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
185 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 ...