Questions tagged [postgresql]
PostgreSQL is an open source object-relational database system.
3,100
questions
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 ...
