Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
0
votes
0answers
26 views

Storing .tif and .tfw files as GeoTIFF raster in PostgreSQL

I have two files .tif and .tfw with same first name. I want to convert them to form GeoTIFF and store it as raster type in PostgreSQL database. Im asking for the way to combine tif and tfw to create ...
0
votes
1answer
41 views

How can I use my NetCDF file in my PostgreSQL database?

I have a NetCDF file which is 25 by 20 grids by latitude and longitude and contains hourly rainfall data at each grid. In total I have 780 hours worth of data. I was trying to use ArcGIS to create a ...
1
vote
0answers
25 views

Splitting a Polygon by a MultiPolygon is unsupported. Both geom columns are MultiPolygon

I'm trying To st_split() on two tables. the output is like this: Splitting a Polygon by a MultiPolygon is unsupported . Although both fields are Multipolygons. Created new columns where the geometry ...
0
votes
1answer
26 views

PostgreSQL hstore filter OSM data

From the file osm.pbf I created a shape that contains lines of a street network and attributes for 'lanes'. Problem: The attribute 'lanes' is combined with many others in a column "other_tags". ...
1
vote
1answer
36 views

Postgres ST_Distance_Sphere Runtime Improvements

I need to select all entries from a table, that are within a distance from a point. Each entry is a point geometry (I set a gist index on geom). Current runtime is anywhere between 3-15 seconds, ...
1
vote
1answer
44 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
24 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
42 views

Converting 10m .tif file to 250m pixel using PostgreSQL functions and store it as PNG [closed]

I have to convert a downloaded 10m pixel .tif file to 250m pixel using postgresql functions and store it as PNG in PostgreSQL database. I'm getting 10m pixel file which I have to convert to 250m and ...
0
votes
0answers
41 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
40 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
34 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
28 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
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
93 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
29 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
34 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 ...
1
vote
1answer
39 views

Mean coordinates of polygon in PostgreSQL

I need a way to get the mean (or median) coordinate of a polygon. With QGIS this is quite simple. But I need to do it with PostgreSQL. There is the expression "st_GeometricMedian". But this only ...
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
53 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 ...
2
votes
1answer
38 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
47 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
27 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
32 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
54 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
46 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
81 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
48 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
200 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
83 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
25 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
96 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
64 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
50 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
116 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
110 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 ...