Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
4
votes
1answer
105 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
32 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
40 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 ...
0
votes
1answer
21 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 ...
3
votes
1answer
129 views

Unable to read TIFF file with raster2pgsql

I am having some trouble with the raster2pgsql function (on a Windows 7 in the OSGeo4W shell). I am receiving the error "Unable to read raster file". I can import shapefiles, ok using org2org so I ...
1
vote
1answer
164 views

'UPDATE 0' results for ST_Within query - locating points within polygons

tldr; I cannot get ST_Within to find business points that fall within two sets of polygons. I keep getting zero results. All three tables have geom col with SRID 4326. I have a data table where I ...
0
votes
1answer
423 views

PostgreSQL Query which returns geom column as WKT set in function

Given: We are accessing a PostgreSQL DB from a VB NET application. When the query completes the DB rows are delivered in a VB NET DataTable. The Postgre DB geom column is in the format of System.Bytes[...
1
vote
1answer
19 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 ...
0
votes
1answer
969 views

Migrating PostgreSQL table with Lat Lon columns to existing PostGIS database [closed]

I am having Postgres database table with Lat Lon columns (degree, minutes, seconds) as shown below : CREATE TABLE population ( id double precision, idno text NOT NULL, place text, state text, ...
2
votes
2answers
1k views

Unable to upload large vector file to Postgis error:Geometry type (MultiSurface) does not match column type (MultiPolygon)

I am unable to upload this large vector file of 30GB(Multipart polygon in ArcGIS FGDB). ogr2ogr throws the following error Warning 1: Geometry to be inserted is of type Multi Surface, whereas the ...
3
votes
1answer
994 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 ...
1
vote
1answer
44 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 ...
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 ...
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 ...
1
vote
1answer
78 views

Speed up the shortest distance calculation using pgRouting + PostGIS

I have to calculate the shortest path and corresponding distances for 300k+ origin-destination (OD) pairs. Using the query below it takes me around 0.3 sec to calculate one distance and therefore more ...
1
vote
1answer
35 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
63 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
120 views

PostGIS and connection to QGIS issue?

I have a problem with PostGIS and a connection to QGIS. I successfully connected to a database on a server but when I try to add a PostGIS Table, I am getting warning symbols next to all my tables. I ...
1
vote
1answer
49 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
57 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
votes
1answer
320 views

Creating Layer editing/Analysis in ArcGIS web map application using Javascript API [closed]

I want to make an ArcGIS web map application in which user can select different layers of the published map (data of the layer in Postrgresql) and do some analysis based on layer data.import data to ...
0
votes
1answer
158 views

Problem to convert geometry to coordinates of geometry(MultiLineString) data

I am stuck converting a geometry(MultiLineString) into coordinates. The following I did. I download the data from this Austin Contours data link I import the shape files using PGAdmin III and ...
2
votes
1answer
62 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
26 views

Using features of CARTO Builder to dynamically calculate the average of selected columns

I am trying to find a simple way to calculate the average of selected columns (avg per row) dynamically in CARTO, using only the pre implemented features of CARTO Builder so that it is as user-...
1
vote
2answers
518 views

psycopg2.ProgrammingError: function does not exist

I am using Python code cur.callproc('function_a',[parameter1, parameter2]) for executing one function and this code is correct when I use a user having full DB permissions. But when I use a private ...
3
votes
1answer
147 views

Getting the largest rectangle in the R-tree index of a PostGIS table?

Is it possible to use SQL or PL/pgsql to get the largest rectangle in the R-tree index (or other types of indices) of a table, so that it can be used as Bounding box for PostGIS table? If possible, ...
1
vote
1answer
72 views

Insert QGIS processed raster image (e.g. GeoTIFF) to PostgreSQL/PostGIS database by pyqgis

I've already connected to my database by a script, and loaded a vector a layer to QGIS. Is there a way to upload a raster layer back to the database? tablename = "***" geometrycol = "***" from qgis....
1
vote
1answer
40 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
2answers
43 views

Loading GeoDataFrame to PostGIS when GEOM column contains MULTILINESTRING and LINESTRING

How can I load GeoDataFrame (populated from shapefile) to PostGIS when GEOM column contains MULTILINESTRING and LINESTRING e.g.: LINESTRING (14.2384666 46.6304714, 14.2386703 46.6304123) ...
4
votes
1answer
338 views

Fix slivers (Holes) between polygons ( generated after removing spikes)

I am trying to fix topological errors in this Shapefile of small areas of a state (Bahia) in Brazil. After applying prepair and PPrepair, the resulting shapefile has some spikes: It looks like a line ...
1
vote
1answer
84 views

Failing to JOIN ON ST_Contains

I have two tables of polygons: Table_1and Table_2. All the polygons of T1 are inside the polygons of T2. I want to SELECT the id of T1 and the name of the polygon from T2 where the polygon is located....
1
vote
1answer
45 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 ...
5
votes
0answers
162 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
71 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('...
4
votes
1answer
2k views

How to force Postgres to use an index?

I have the following query that finds all id inside POLYGON(({points})) SELECT id FROM t WHERE ST_DWithin('POLYGON(({points}))', ST_Point(latitude, longitude), 0); The query is slow because it doesn'...
7
votes
1answer
568 views

Display pictures stored as bytea in postgresql column in qgis

does someone knows a possibility to visualize in a bytea column saved photos of a postgresql database in qgis? I would like to store pictures directly into the database, not just the link/ url.
1
vote
0answers
217 views

Problems using pgr_fromAtoB - postgresql error

I have some trouble with the pgr_fromAtoB function of the workshop. I get: ERROR: column "ways" does not exist But "ways" is the table, not a column. What am I do wrong? My table ways looks like: ...
2
votes
0answers
255 views

Querying after OSM Database created [duplicate]

I imported an OSM file into a Postgres DB using the Osmosis tool. Now, I need to extract some data but I can't find any schema description (I'm really a newbie in GIS). Where can I find it? Do ...
0
votes
0answers
34 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 ...
5
votes
3answers
5k views

Postgis ST_Intersects performance

I am selecting a lot of polygons within a gigantic polygon by using the postgis function ST_Intersects(). SELECT * FROM data.polygons AS poly, data.gig_polygon AS gig ...
6
votes
2answers
13k views

PostGIS ST within does not exist

I'm quite new to postgresql and postgis so this error will probably due to some rookie mistake. I want to run the spatial query ST_within. When I describe the query like this SELECT nw.entity_id AS ...
3
votes
1answer
74 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 ...
1
vote
1answer
156 views

PostGIS geocoding with local street centerline data

I have the PostGIS TIGER geocoder setup and working to geocode addresses in a single city. I am trying to use local street centerline data for geocoding instead of the TIGER data because it is more ...
2
votes
2answers
89 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 ...
5
votes
7answers
6k views

PostgreSQL Shapefile import failed

I am very new to PostGIS/PostgreSQL. In fact, I downloaded it because of the answers I got to this question: Distance between centroid and farthest point of polygon I installed both PostgreSQL and ...
1
vote
2answers
62 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, ...
0
votes
1answer
160 views

Text File MultiPolygon/Polygon to PostgreSQL using Python/Psycopg2

I am writing a python script attempting to insert GeoJSON Polygon coordinates into a Postgres table. I am writing a basic insertion code since I am new to GIS domain. The code look like this: ...
0
votes
1answer
108 views

What to do after running osm2pgsql

I must admit that I'm new when it comes to database administration, but I'm a little confused at the process of my getting my .osm.pbf file into tilemill. I've run the osm2pgsql process on a .osm....
1
vote
0answers
37 views

Raster Average Across Tiles

I have a raster that is tiled and I am looking to produce an average raster with a 3x3 window. I have tried three methods. The first method uses the standard ST_Mean4ma. An average raster is produced, ...
2
votes
1answer
310 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?

1 2 3
4
5
64