A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
-1
votes
0answers
13 views

Clarification on GeoServer Function and how it interacts with databases [closed]

I am currently working on a web application which uses OpenLayers to display PostGIS layers served through GeoServer. I have two points of confusion. Do the SQL Views described here support PostGIS ...
1
vote
1answer
38 views

How to merge 2 huge tables with only geometry in common without duplicates

I've got 2 tables with several millions of rows. Because of poor data management, they don't have any ids at all or anything that helps match them up except for the geom I've tried doing insert into ...
1
vote
0answers
18 views

Saving TIFF file in database with GeoDjango

I am trying to save a TIFF file in database with GeoDjango, but I get weird errors at dem.save(). Versions: Python: 3.7 PostgreSQL: 11 Django 3.0.2 django.contrib.gis.gdal.gdal_version(): 2.4.0 ...
0
votes
0answers
34 views

Connect QGIS to Remote PostgreSQL Server

I am trying to connect to my database on a remote server using QGIS. From what I've read I have to edit the pg_hba.conf and the postgresql.conf files. I think I have edited these files to allow any IP ...
0
votes
0answers
37 views

ArcGIS Pro failed to connect to PostgreSQL 11 database

I'd like to connect from ArcGIS Pro 2.4.2 to a PostGIS database in PostgreSQL 11.2. I am able to access via pgAdmin 4 to the respective databse. The link and access with my credentials is working for ...
2
votes
1answer
31 views

Trying ST_Slope with SRTM rasters and a shapefile but the output raster has different SRID

I have two SRTM rasters https://dds.cr.usgs.gov/srtm/version2_1/SRTM1/Region_06/ (N40W074 and N40W075) with Datum "WGS_1984" I've united them to be able to work with one raster: CREATE TABLE rasters....
1
vote
0answers
27 views

Load PostgreSQL database from corrupt drive [closed]

I suffered corruption of a hard drive but managed to recover files for a PostgreSQL server. We are running on the assumption that the files themselves are not corrupted (yet to be able to tell). Is ...
1
vote
1answer
36 views

Updating certain values in a MATERIALIZED VIEW [closed]

I have created a materialized view and now want to replace a certain value in one of the columns. How can I do that? Perhaps I should use INSERT INTO, but I do not really know how to use this. The ...
-1
votes
0answers
16 views

Having problems with SQL View using DVF [closed]

I'm working on a project as student and my goal is to create a web api for a real estate agency. Im using a database called DVF provided by the country (FRANCE). I'm new to geoserver and its been ...
1
vote
0answers
33 views

Features of PostGIS layer are only selectable in attribute table but non on map canvas

My features of a PostGIS layer are displayed in the map canvas in QGIS and projected properly at the right place. They are not selectable in graphically in the map. But they are selectable in ...
4
votes
1answer
90 views

Using `CAST` and `ST_UNION` in same query in PostgreSQL

I want to union the geometry of waterlines grouped by a table column in a new view. My problem is that I apparently needes to specify the geometry type using CAST function. But I cannot figure how to ...
0
votes
1answer
28 views

Merging three linestring tables into one new table view [closed]

I have three tables in a PostGIS database and I want to create a table view merging the features from the three tables into one, by using PostgreSQL. The three tables are containing three different ...
1
vote
1answer
31 views

Error when creating PostGIS extension in pgAdmin4

I am trying to create a PostGIS extension in pgAdmin4. When typing CREATE EXTENSION postgis;, or through "db_name > Create > Extension > General > Name = postgis", I get this error: ERROR: could ...
2
votes
3answers
71 views

How to convert UK grid to coordinates in PostgreSQL?

I have a very large file with UK grid references that I need to get coordinates from I'm on a Mac so I cant seem to use the solution provided in this link https://digimap.edina.ac.uk/webhelp/os/...
1
vote
1answer
31 views

QGIS: Prevent the Primary Key from incrementing when feature won’t be saved

So, I have a PostgreSQL/PostGIS DB attached to my QGIS project (MacOS, 3.10.). I start adding a feature and cancel the process w/o saving the form. When adding another feature my primary key ...
0
votes
0answers
35 views

Importing shapefile into PostgreSQL failing [duplicate]

I have installed PostgreSQL 12 on my Windows 10 OS. I installed the PostGIS extension as well. I would like to import a shapefile and am using shp2pgsql-gui to do so. The import is failing and there ...
3
votes
1answer
46 views

GeoPandas to PostGIS database

New to using GeoPandas instead of Pandas. I am trying to export a GeoPandas dataframe to a PostgreSQL database (with PostGIS extension). The import is from the same database as I try to export to ...
1
vote
0answers
32 views

QGis 3.4: GPKG vector layer with object form (relations with spatial and non-spatial datasets) to postgres database

I have QGis project with a geopackage included. One layer in the geopackage contains a attribute form with relations to other layers (spatial and non-spatial). I have to convert it to a postgres ...
1
vote
1answer
44 views

Convert PostgreSQL geometry to Esri ST_Geometry

I am trying to create a spatial view in ArcMap where I run a ST_Intersect against a ST_Geometry point dataset and a PostgreSQL geometry polygon dataset. Both datasets are the same SRID. I am having ...
1
vote
1answer
26 views

PostGIS - “parse error - invalid geometry” after using ST_Multi - but ST_IsValid returns true

I have a database with OpenStreetMap polygons, points and relations in separate tables. Now I wanted to build selection queries for the different features and created schemas for each zoom level (5k, ...
1
vote
1answer
30 views

Accessing PostGIS geometry with psycopg2

I've a PostGIS database loaded with several tables filled with shapefiles. I'm using psycopg2 to access everything via python. I'm able to access the table names with the below query: """SELECT ...
1
vote
1answer
50 views

Using views with 1:N relation QGIS

Working with PostgreSQL basic view in QGIS I noticed that: Select * from t1 where t1.column = 'some_condition' Views doesn't support constraints according to this topic: https://stackoverflow.com/...
1
vote
1answer
35 views

Select points within a buffer around a line

Working with PostgreSQL. I have a Table with streets (Linestrings), and a Table with accidents (Points). The streets are divided into sections. I want to find the accidents within 50m around a certain ...
1
vote
1answer
39 views

PostGIS shapefile import manager error: syntax error

I'm trying to use PostGIS Shapefile Import/Export Manager to import a shapefile that was created in QGIS. The shapefile has all the blocks of the 2010 U.S. Census for Oregon and was exported as a ...
1
vote
1answer
32 views

Length of a street-Spatial SQL

I have a table with the geometry of streets and I`d like to find out, which is the longest and which length (in kilometers) it is. How can I summarize the individual street segments?
2
votes
1answer
53 views

Perform intersection using a TRIGGER

I've two geometry table inside my DB: buffer: id INTEGER SERIAL PRIMARY KEY, geom POLYGON polygons: geom MULTIPOLYGON I need have the intersection between buffer and polygons and I will do this ...
0
votes
1answer
43 views

Loading Geodataframe to PostgreSQL with psycopg2 connection

I have created a Geodataframe from a PostgreSQL table with the following instruction (where 'con' is a psycopg2 connection): gdf = gpd.GeoDataFrame.from_postgis("SELECT id, geom FROM {0}".format('...
0
votes
0answers
27 views

Extremely slow Nominatim install with Docker

I am trying to install Nominatim based off of a dockerized version of the tool found here: https://github.com/mediagis/nominatim-docker. I was successful with installing dockerised Nominatim with a ...
3
votes
1answer
50 views

QField App crashes when connect to internal server of PostgreSQL and PostGIS

I'm completely new to GIS field and here what I'm trying to do: I want to make the QField App connects to internal server which has a PostgreSQL and PostGIS running on it. I created a schema in ...
2
votes
2answers
80 views

GeoServer parametric SQL view. ERROR: function key(boolean) does not exist

I have been had a task: to allow a single WFS request to return two different responses depending on zoom: return usual SELECT * FROM table if zoom level >n, and return SELECT ...
1
vote
2answers
46 views

How to use SQL view request for WFS from GeoServer: Apply PostGIS function depending of zoom

I have a task to allow a single WFS request to return two different responses depending on zoom: return usual SELECT * FROM table if zoom level >n and return SELECT ST_SimplifyPreserveTopology(geom, ...
2
votes
1answer
185 views

ST_Intersects vs ST_Within

Assuming that I have a point on a map and a multipolygon on a map. When I query with ST_Intersects or ST_Within I get the same result. I want to know what is different between these two methods?
0
votes
0answers
39 views

RAISE NOTICE from trigger to QGIS

I work on vue postgresql made with table, so for save I have to use trigger. My trigger do a lot of thing and I have to send message to the user to make him understand what happend. But QGIS ...
1
vote
0answers
27 views

Restrict editing access to my PostgreSQL/ PostGIS database used through QGIS [closed]

We have a small network of users accessing our QGIS data stored on a PostGIS database which sits on a 'Q' drive in my computer. This drive is then shared with the others. Due to the other users being ...
2
votes
1answer
75 views

OSM + PostgreSQL Queries

I'm newbie to PostGIS and am looking for some SQL examples for better understanding. I didn't understand how do I manipulate the tables, how objects relate one to another. An example: There is this ...
3
votes
0answers
45 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 ...
0
votes
0answers
24 views

Problem with osm2pgsql

I'm having a problem importing a .pbf file in PostgreSQL using the osm2pgsql. I've downloaded the italy-latest.osm.pbf and as I do every year I'm going to upload it in PostgreSQL. I did it for other ...
1
vote
1answer
88 views

How do I check if the PostGIS extension to a PostgreSQL data base is working in DBeaver?

I am starting with PostgreSQL and trying out a few things in a small testing DB-server. I have connected this DB DBeaver 6.2.2 and added a few tables and columns. Now I wanted to get some spatial data ...
0
votes
1answer
59 views

Are DBeaver/PostgreSQL Datatype = Polygon real PostGIS spatial Objects?

I am currently designing a database for my PhD-Project. I want to link a PostgreSQL database to QGIS. I just started in PostgreSQL and am using DBeaver 6.2.2. When creating my first Test-DB I saw, ...
1
vote
1answer
49 views

Find Closest Route from single source to several different destinations in PostgreSQL

I've made network nodes in PostgreSQL SQL based on Osm2po. I can find the closest way among two points based on the pgr_dikstra algorithm but I want to find the closest route between one single source ...
0
votes
0answers
36 views

Using PHP on Webservers and PostgreSQL

I'm creating a general webapp that is displaying data from a Postgres database hosted on Digital Ocean. My problem is that I can't get the PHP script to run on my webserver(hosted by Hostgator) to ...
4
votes
1answer
111 views

PostGIS erase/difference vector polygons from raster

I have a raster and a vector table in Postgres 12 PostGIS 3.0 The vector polygon(orange) overlaps the slope raster(grid in black, whiteish) All I want to do is cut out/erase the orange polygon from ...
2
votes
0answers
28 views

ogr2ogr sql query from dbf

In ogr2ogr, I can successfully read *.DBF files and write them to PostgreSQL DB: SET DBCONNECT=host=bla port=blue dbname=blub user=me password=easy active_schema=now SET PGCLIENTENCODING=UTF8 SET ...
1
vote
1answer
18 views

How to create updateable/inseartable view on QGIS with a sequence id?

I have a huge point dataset that takes a long time to insert new stuff when using QGIS This table has an id column of type serial To go around this problem I've created an empty view that has the ...
5
votes
0answers
104 views

ST_Covers fails on GEOGRAPHY column with GiST index, PostGIS 3.0 [closed]

I'm trying to upgrade to Postgres 12 with PostGIS 3.0. Unfortunately, I can't get my spatial queries to work. There seems to be an issue using ST_Covers on a geography column with a GiST index. Proof ...
0
votes
1answer
19 views

Query to Map GPS points with multi-linestring

I have a column of geometry of type multi-linestring in a database as in the following format Now I want to add a new column of speed by mapping GPS points against geometry column (as described ...
2
votes
1answer
52 views

How to retain the explicit geometry type of a column in a database view of PostGIS?

I try to retrieve "generalized" table's info for a long-term backup project from a PostgreSQL database version 11, with the PostGIS 2.5.3 extension under Freebsd 12.0. I want to use the PostgreSQL ...
1
vote
0answers
20 views

ArcGIS Pro PostgreSQL SSH connection string

I am involved in a project where a SQL Server-base application is being retired and replaced with a PostgreSQL/PostGIS-based tool under development. There is a large amount of cross-checking to be ...
2
votes
1answer
31 views

How to combine GEOMs or linestrings from different rows

I am working with a set of data that includes common IDs and overlapping linestrings/geoms. I would like to combine the overlapping lines based on their common IDs, preferably in postgres. I can run ...
0
votes
0answers
21 views

CartoView Error

I've installed CartoView on Windows 7 by default instruction. I've changed Postgres to my own DB (It was problem with default DB from install package). CartoView doesn't start with this error: I ...