Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

Filter by
Sorted by
Tagged with
1
vote
1answer
49 views

Can I use ST_Within or ST_Contains to update columns based on a geom being within another?

Based on the following tables: Street Municipality I'd like to populate Street.NOM_ENT with Municipality.NOM_ENT and Street.NOM_MUN with Municipality.NOMBRE any time Street.geom is within ...
4
votes
1answer
261 views

raster2pgsql not in C:\Program Files\PostgreSQL\12\bin

After adding both C:\Program Files\PostgreSQL\12\bin C:\Program Files\PostgreSQL\12\lib to my windows environmental variables PATH thing, I can't access raster2pgsql: 'raster2pgsql' is not ...
1
vote
0answers
13 views

Clip PostGIS raster to region that crosses 180 meridian

I am using postgis 2.5's raster to store global geographical data (weather forecast) in EPSG:4326 projection, using 0.25° resolution. I use tiling. I created queries to clip the global data to given ...
2
votes
1answer
45 views

Label every polygon with true/false if it contains point

I have large polygon data set and I want to label every one with true if polygon contains point or false if it does not. Polygon can contain multiple points, but if just one point fall into polygon it ...
3
votes
1answer
82 views

Converting ESRI File Geodatabase (FileGDB) to PostGIS

I am trying to convert ESRI FileGDB to PostGIS I created a new database: 'newkelibia' with pgAdmin 4 using OSGeo4W shell I executed this: ogr2ogr, -f "PostgreSQL" PG:"host=localhost ...
2
votes
0answers
45 views

PostGIS Query using ST_Intersects not using spatial index

As code is better than 1000 words, here we go : explain analyze SELECT DISTINCT(zones.office_id) AS office_id FROM "zones" JOIN offices ON zones.office_id = offices.id WHERE ST_Intersects('...
3
votes
1answer
117 views

Using related layers/tables in background?

I have postgresql/postgis database setup with one to many relationship. I can successfully set it up in QGIS but i would like to keep related tables hidden from Layers Panel to keep the project "clean"...
0
votes
0answers
21 views

PostgreSQL/PostGIS cast geometry column of joined table to varchar while creating another table

A simple SELECT on a table with a geometry column works, but creating a table from this query automatically cast the column to varchar. Here is the query: CREATE TABLE importe AS SELECT fid, id, ...
3
votes
1answer
343 views

Problem with checking for Valid Geometries for PostGIS

I have used this PostGIS in different environments many times without issue: update my_table set geom = ST_MakeValid(geom) where ST_IsValid(geom) = false; However in Azure I keep seeing the ...
0
votes
0answers
25 views

shp2pgsql append 3 Shapefiles with different geometry types and a few different attributes to a single table

I'm successfully inserting data to 3 PostgreSQL tables using shp2pgsql as follow: shp2pgsql -s 2056 points.shp my_schema.my_points_table | psql -q -d ${POSTGRES_DB} -U ${POSTGRES_USER} shp2pgsql -s ...
4
votes
1answer
97 views

Reprojecting in PostGIS not working as expected

I just reprojected with PostGIS tree layers using this function: select UpdateGeometrySRID('schema', 'table', 'geom', 3857) These tree layers are contiguous. The problem is, when I open it on QGIS to ...
4
votes
1answer
184 views

Loading vector layers from database/ schema using login username and password in QGIS?

I have a QGIS setup with a PostgreSQL / PostGIS database on a server and multiple clients. I have created schemas in the database for each user. With a username password combination, each user is ...
3
votes
1answer
44 views

ogr2ogr cannot import into table with two geography columns

I am using ogr2ogr to import geojson files into a PostGIS table. The table has two geography columns ("footprint" and "centroid"). The geojson files contain polygon features, and I ...
1
vote
1answer
56 views

Polygon to multi polygon

Following this treat https://stackoverflow.com/questions/67972764/alternative-first-function-for-geometry-type , im trying to eliminates duplicates from a table using group by. For the aggregation ...
2
votes
1answer
53 views

Polygon intersection giving a GeometryCollection of only LineStrings, and the wrong dimension

I have a case where I want to create a difference between two geometries, and store it my db. I later need to check for conflict between geometries in the db, and for that I check two things: if there ...
2
votes
0answers
57 views

QGIS server: “API not found error: collection was not found” when reaching the /collections endpoint of the OGC API - Features specification

I am trying to set up a QGIS project (my_project.qgs) to server data using the OGC API - Features, namely the WFS-3 protocol. For this purpose, I'm using this Docker image.. When trying to access my ...
2
votes
1answer
41 views

Reloading attributes of selected item using GeoServer/OpenLayers

I have a PostGIS database with items whose geom is an ST_Point, so they show as a clickable point when shown in GeoServer/OpenLayers. Each of them has around 90 attributes and the database is ...
3
votes
1answer
79 views

Getting the 'composite' of two polygon layers

I have two table of polygons, and I want to 'flatten' them into one table, and have the result tell me which areas are in one table of polygons, which in the other table of polygons, and which are in ...
4
votes
1answer
734 views

Backup QGIS (3.2.1) layer to independent PostGIS table?

I'd like to make multiple, progressive backup copies of QGIS (3.2.1) layers that are stored in PostGIS tables. I've tried multiple routes to export them as files on disk, but they all seem to destroy ...
0
votes
1answer
47 views

Joining and Updating PostGIS data using QGIS

I have a PostGIS database with which I am currently using QGIS to manage the layers. Say I have a layer in PostGIS that I want to do a join to to add new data in. Is it possible to do this join in-...
2
votes
1answer
74 views

PostGIS, closest point to another point

I have two tables: point1 and point2. For each point from table point1, I want to find the closest point from table point2. For example: For point with id=1 from table point1, the closest point from ...
7
votes
0answers
142 views

Intersections in linestrings using PostGIS

I need to get the intersections of streets and create a graph, so to get the intersections I use this select: SELECT ST_GeometryType(geom), geom FROM ( SELECT a.gid, ST_Intersection(a....
3
votes
1answer
252 views

Slow retrieve PostGIS raster value through ST_Value

I have a PostGIS DB containing a set of daily raster data from 1981 up to 2021. The inserted raster tile size is 83x90. Problem: I wanna query the data through ST_Value for a specified Lat Lon, ...
1
vote
1answer
38 views

Error in alter column ST_Dump PostGIS

I alter multilinestring geometry column to get linestrings geom with this syntax: ALTER TABLE jalan_diy ALTER COLUMN geom TYPE Geometry(LINESTRING, 4326) USING (ST_Dump(geom)).geom ; and I got an ...
8
votes
2answers
2k views

calculate slope for a road network

I am working on a road network for routing operations using postgis and QGIS. I am new to both of these tools and need to calculate the slope for each edge (by retrieving the Z value for the start ...
5
votes
1answer
87 views

ST_ Intersects vs ST_DWithin behavior on polygons using PostGIS

I have a large table with millions of current and historical building footprint polygons and need to efficiently fetch all of these that fall fully or partially within a user specified set of search ...
3
votes
2answers
867 views

Listing all overlapping polygons using PostGIS?

Assume a table of overlapping polygons where the number of polygons overlapping a single area varies from 1 to n. I am trying to find a PostGIS/SQL query that will return a list of all of the polygons ...
1
vote
2answers
34 views

How to efficiently fetch multiple ST_Value from PostGIS raster(s)

I have a raster table with index created by raster2pgsql CREATE TABLE tbl (rid serial PRIMARY KEY, rast raster); CREATE INDEX ON tbl USING gist (st_convexhull(rast)); I can see the best way to fetch ...
4
votes
1answer
210 views

Upgrading PostGIS and PostgreSQL but running upgrade to PostgreSQL it errors?

I want to upgrade PostGIS(2.0.4 ---> 2.2.3) and PostgreSQL (9.2.4 ----> 9.6) so First I upgrade PostGIS 1、install PostGIS 2.2.3 on postgresql 9.2.4 2、alter EXTENSION postgis 2.0.4 ---&...
1
vote
1answer
44 views

St_buffer then reverse st_buffer

A slightly theoretical PostGIS question. If you have a polygon, perform a positive buffer on it, then perform the same buffer operation but negative, you end up with a slightly different polygon than ...
2
votes
1answer
264 views

How to polygonize using GeoDjango and PostGIS?

The code of GeoDjango-1.9.2 contains reference to the function ST_Polygonize at: https://github.com/django/django/blob/master/django/contrib/gis/db/backends/postgis/operations.py#L132 But is it seems ...
5
votes
2answers
11k views

MapServer very weak performance vs. GeoServer - Why?

I search for a good WMS server that support PostGIS at the end I found MapServer as the fastest and best choice. Every performance test say it's faster than GeoServer because of its native compiled ...
4
votes
1answer
237 views

Creating consistent topology using PgRouting

I'm developing an application that needs routing information for certain cities. First, I downloaded a openstreetmap datafile (*.osm) and then I imported it into a PostgreSQL database using ...
1
vote
1answer
31 views

ST_Segmentize - segmentation and duplicates

I did a segmentation on a layer of lines and then I transformed this segments to point dividing the line in 5 parts indepentently of his lengh. The problem I have, is that some of them, insted of ...
1
vote
1answer
83 views

How to create an editable personal copy of a read-only PostGIS layer in QGIS 3.4

I have a few vector layers in a PostGIS database. I want to give the other users of the database read access to these layers. I don't want them to be able to alter them, though. Instead, if anybody ...
1
vote
1answer
71 views

Combining points into polygons with range of values using ST_ClusterDBSCAN and ST_ConvexHull

I tried to create polygons with similar depths based on values from point data. I got some help to start with in this question Combining points into polygons with range of values using PostGIS Since ...
1
vote
1answer
55 views

Create a clip in processing script

I am trying to create a simple script for me to clip the layers of a directory. The moment I create an output directory it no longer creates the interface of the tool. Additionally, I'm not sure I ...
2
votes
1answer
32 views

PostGIS sfcgal ST_3DArea

I want to compute surface areas of a large CityGML data set. However, I ran into some issues, which I could not resolve or fully understand. The error message is the following: Polygon is invalid : ...
3
votes
1answer
35 views

Add Z value to an existing 2D point in PostGIS

I'm facing this query which is used to extract a centroid point (3D) of a 3D polygon resulting from the cartographic intersection of a 2D polygon given by the user of an application (which is inserted ...
2
votes
1answer
47 views

Why does my source and target repeats itself when I use pgr_createtopology()?

I created my path in QGIS v2.18.18, making the lines and a combination of cleaning tool and explode lines tool to get my desired output. I tried using ROAD GRAPH plugin in order to check whether my ...
2
votes
1answer
69 views

geopandas to_postgis: ValueError: geom (geometry(MULTILINESTRING,4326)) not a string

I have a GeoPandas GeoDataFrame with a column named geom having MultiLineString values as such: print(gdf.to_markdown()) # using package 'tabulate' to display a markdown table | | uid | key | ...
2
votes
0answers
39 views

PostGIS trigger to create line in one table when line is created in second table

I am trying to migrate to PostGIS from shapefiles and I've started setting up the trigger inside. I manged to create trigger which are populating all of the geometry fields (length, Cartesian ...
1
vote
1answer
35 views

PostGIS: Automatically updating geometry field in a table after updating lat long values

It's simple to create a view from a table that generates a valid geometry field: create view mywgs84points as select row_number() over (order by id) as gid, st_setsrid(st_point(lon, lat), 4326) from ...
2
votes
1answer
57 views

Saving ST_Envelope result gives error: Geometry type (MultiPolygon) does not match column type (Polygon)

I'm trying to save the bounding box of a multipolygon into a polygon column like this: UPDATE "mytbl" SET bb = ST_SetSRID(ST_Envelope(mp::geometry),4326) WHERE "mytbl"."id" = 1 But I receive the ...
6
votes
3answers
4k views

Create a Rectangle around a point in PostGIS

I have just started working in PostGIS. I have a table in PostgreSQL that stores Latitude,Longitude. I want to create a rectangle of 500*500 foot around every point. Therefore, used the below given ...
1
vote
2answers
309 views

Appending columns from a table to my active table?

I am trying to append three columns from srs2016 to srs2013_1 using this sql. INSERT INTO srs2013_1 (the_geom, agency_responsible, description) SELECT the_geom, agency_responsible, ...
2
votes
1answer
46 views

PSQLException: ERROR: operator does not exist: public.geometry && public.geometry

I have database with PostGIS already installed. I am running the following query from pgAdmin and I am successfully able to run it without any errors. But when I run it with Java, I am getting the ...
1
vote
1answer
68 views

Saving borough boundaries as MultiLineStrings or MultiPolygons?

I am creating a database in PostGIS and I have a column that will save the boundaries of boroughs. The app will later query the database, asking what points, lines or polygons are inside those ...
2
votes
1answer
59 views

How can I optimize this query PostGIS

I have 2 spatial tables: Streets: linestrings of streets from a country (more than 3 million) Portals: points of adress from a country (around 18 million) I need to move each point to the closest ...
4
votes
3answers
835 views

Boolean fields from PostGIS data type changed to QString in QQIS

I have a layer in QGIS loaded from a PostGIS database. In the layer properties dialog in QGIS, under the fields tab, layers with a typename of 'bool' (which are boolean fields in the PostGIS database) ...

1
2 3 4 5
163