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
0
votes
0answers
5 views

Why there's no open layers map showing below the points when I have loaded it perfectly geoserver?

Why there's no open layers map showing below the points when I have loaded it perfectly geoserver?
1
vote
1answer
32 views

Is a spatial index on a ST_Transformed geometry simply ignored when the transformation has the same target CRS than CRS of the geometry?

I have such vanilla index on a PostGIS table: CREATE INDEX polygons_gix ON public.polygons USING gist (geom); Because of this: If using more than one transformation, it is useful to have a ...
2
votes
1answer
67 views

Optimizing an intersection between a single massive multipolygon (WKT) and many features from PostGIS

Context I have a massive MultiPolygon (~90'000 vertices, super complex, spreading over 60km², with lots of holes and parts) resulting of some Python computation. This MultiPolygon is coming as a WKT ...
2
votes
1answer
52 views

Loading PostGIS layer within active layer extent using PyQGIS

I think what I am trying to do is quite simple..however, I couldn't figure out how to do this. I am fairly new to PostGIS and PyQGIS. I've created a spatial table with PostGIS that contains vector ...
1
vote
1answer
49 views

PostGIS ST_Intersects replace NULL value with 0

I'd like to count the number of points that interact with a given polygon using PostGIS. Below is my query SELECT DISTINCT COUNT(pt.geom) OVER w sum_of_pt FROM pt, poly WHERE ST_Intersects(pt.geom, ...
1
vote
0answers
26 views

How to import PostGIS into BigQuery GIS?

I have to import PostGIS data into BigQuery GIS. In order to do this, I dumped the PostGIS data in CSV and import it into BigQuery. This is working for all the columns but not the geometric one. If I ...
4
votes
0answers
36 views

ST_LocateBetween results into MultiLineString cannot contain MultiLineString element error

I found a problem while calculating the geometry between two measures using line string geometry with M coordinates. Query execution results into [XX000] ERROR: MultiLineString cannot contain ...
2
votes
0answers
45 views

Edited WFS layer was not saved in Database

I managed to load the WFS and my basemap successfully and managed to get the buttons to show up. The buttons appear correctly like in the working example , for some reason the geometry data isn't ...
0
votes
0answers
27 views

Iterate througn [Java.lang.double in JS

I have a Leaflet app with PostGIS and Geoserver at the server side. Some columns in PostGIS tables are of type Double[] and contain arrays of double values. I need to get these arrays into my app and ...
3
votes
1answer
33 views

View two geometry columns together in pgAdmin Geometry Viewer

I have two geometry columns like this: How can I see them together in the Geometry Viewer? If i select all rows it shows only one column at a time.
1
vote
0answers
61 views

PostGIS - ST_Union + ST_Difference create extra line segment

Very similar to this question I have two multipolygons, which touch/overalp each over. please see this link for the multipolygon #1 and multipolygon #2 multipolygon #1 multipolygon #2 I ran st_union ...
0
votes
1answer
26 views

Very Slow OSM Tile Rendering

I have built my own OSM server of North America using switch2osm's Ubuntu 20.04 guide and all went well. Except when it comes to rendering tiles...or pre-rendering. Here is the most recent completed ...
1
vote
0answers
40 views

How to run st_difference on two large multipolygons efficiently

I have two multipolygon tables: t1 and t2. t1 contains 136 000 rows and t2 contains 5970 000 rows. t1 is always overlapping with t2. The goal is to run difference to remove the area where t1 is ...
0
votes
1answer
49 views

PostGIS ST_Intersect - dealing with different coordinate systems

I try to get an intersection of two tables - lines and polygons. Lines' CRS is 3006 (SWEREF99 TM), while polygons' CRS is 4326 (WGS84). I write the query: SELECT v.subid FROM line AS v, polygon AS o ...
1
vote
0answers
16 views

Building, vertical alignment and urban densification [closed]

I am looking for a methodology to select using a land layer (plot) and a building layer the land where the buildings have low height and are located between lots with high buildings. Has anyone ever ...
0
votes
1answer
30 views

PostGIS in shapefile import/export manager - how to add schema

I want to know if it is possible to add the "schema" in "see connection details" I was looking but couldn't find anything.
2
votes
0answers
60 views

Use ST_AsBinary from PostGIS in PyQGIS

I'm trying to read the St_AsBinary from PostGIS and to have a QgsGeometry. I could have it working with this code: import binascii postgis_connection = "qgistest" metadata = ...
0
votes
0answers
48 views

How to get all polygons that are spatially interconnected with each other in PostGIS?

I have the following scenario: SELECT * FROM owner_poly; > |id| owner_id | polygon_ids | |1 | 10 |'{1,2,3,20}' | |2 | 10 |'{12,22,2}' | |3 | 10 |'{1}' | |4 | 10 |'{...
5
votes
1answer
59 views

Applying ST_Intersects in a query with PostGIS

I have a PostgreSQL/PostGIS materialized view (study_region_all) with stream lines and a table of county boundary polygons (counties). They both have a column called co_fips but for a subset of items ...
2
votes
2answers
37 views

How to draw lines from point-to-point, In PostGIS / PostgreSQL

I have some GPS points in PostGIS, and I’m looking for a way to draw lines from point to point, to illustrate travel routes. I know how to do one long line, but I need to do small point-to-point lines....
0
votes
0answers
27 views

Unable to add geopackage to PostGIS database using OGR2OGR

I am using the following command to upload a geopackage into a POSTGIS database using the OSGeo4W shell that comes with QGIS. ogr2ogr -f “PostgreSQL” PG:”host=<hostname> dbname=<dbname> ...
0
votes
0answers
51 views

MVT coordinates are not accurate with less zoom

I'm using MVT data to draw some markers on Google Map. I have all setup "correctly" but I have some problems when I zoom out. This is a screenshot of a zone with more zoom: And the same ...
5
votes
1answer
84 views

ST_Transform error: latitude or longitude exceeded limits(-14)

I am trying to run a query in PostGres on a multiline spatial feature class table. Among other things, I am trying to use PostGIS to get the shape length of the reprojected geometry. My query looks ...
3
votes
0answers
45 views

How to workaround PostGIS 2.5.1 St_Contains and St_Intersects seemingly incorrect

I generate rectangles in several steps and store them in my layer f. I want to check some points p are inside f. I've been using ST_Contains successfully for that numerous times but here apparently ...
2
votes
1answer
58 views

In what system are coordinates from the ST_AsMVT PostGIS function

I have a table (records_addressvalue) in my PostgreSQL database with a geom column (geom) with srid=3857. I have some rows in there, and I'm querying the database to generate MVT tiles. I'm following ...
7
votes
1answer
157 views

Performing merge in PostGIS

By means of the PostgreSQL/PostGIS I want to replicate a function from a Python script into a SQL query. In my Python script, I have a list of feature classes and I use ArcPy's Merge_Management to ...
2
votes
1answer
54 views

PostgreSQL query from large raster table problem

I am trying to build a fast query to PostgreSQL. My raster (DEM) is 1.6 GB, about 2.5 cm precision...With a smaller DEM it was ok, about 20 seconds to query a 300m radius elevations. Now with this ...
2
votes
0answers
23 views

Data warehouse for ERA-5 data

I'm new to GIS an this kind of data. I want to create for my team an easy way to obtain weather data. We are using https://cds.climate.copernicus.eu/cdsapp#!/dataset/reanalysis-era5-land?tab=overview ...
0
votes
0answers
40 views

Transferring/inheriting GIS database structure in PostGIS from old project to new one

I have been given an existing GIS database design for a solar power project, with its structure, relationships, geometry etc designed from another project and existing in PostGIS database. I am ...
4
votes
1answer
35 views

Uploading a geodataframe with some missing geometries using 'to_postgis'

I have a GeoDataFrame with plenty of info, including a 'geometry' column. Some of the rows have no geometry data... I simply don't have it. I decided to leave those rows as 'None' or 'NoneType' in the ...
2
votes
1answer
53 views

Error reprojecting a layer

I have a layer on ESPG 4326 and I want to reproject it to ESPG 3857. I'm using this SQL query: ALTER TABLE table ALTER COLUMN geom TYPE geometry(Geometry, 3857) USING ST_Transform(ST_SetSRID(...
4
votes
1answer
42 views

ST_DWithin is returning all the data in the table

I am new to PostGIS and currently struggling with ST_DWithin function. I have a table stores, which stores lat and long of locations in Denmark in the location column as geometry and with SRID 4326. ...
0
votes
0answers
51 views

Error when using raster from PostGIS: Native SRS empty

i'm using GeoServer version 2.19.1 and PostgreSQL/PostGIS psql (13.4 (Ubuntu 13.4-1.pgdg20.04+1), server 12.7 (Ubuntu 12.7-0ubuntu0.20.04.1)). After importing a raster file, in this case 'dem_spain' ...
0
votes
1answer
44 views

How to convert simple rectangle bounding box to a polygon on the spheroid

Using PostGIS, I need to find certain data within a bounding box. The data is stored as point data in a column. The bounding box is basically the visible area of a map, as a user sees it: However, as ...
1
vote
2answers
33 views

View does not show up in layers dialog of QGIS

I create a View with a long CTE CREATE VIEW foo AS WITH joined_socio AS (... As far as I know, I should be able to visualize Views in QGIS. However, when I go into QGIS -> Layer -> Add Layer -&...
1
vote
1answer
43 views

ST_DWithin() Point to Polygon - What is referenced?

I have a Point geometry and several Polygons surrounding the point geometry. If I use the ST_DWithin() function and give it, lets say - 500m as a parameter. What Polygons return true? The Polygons ...
4
votes
4answers
99 views

Saving modifications in PostGIS layer edited in QGIS

I opened a PostGIS layer (from the server) with QGIS and I did some modifications (for example attribute join from another layer). How can I save my modifications so that it is visible in pgAdmin?
4
votes
1answer
112 views

ST_Intersects with the whole world doesn't bring back all results

I have a table of devices with a column last_known_coordinates (geography(Point,4326)). I want to find devices within a bounding box with a query. Some bounding boxes are working, but others are not - ...
3
votes
0answers
39 views

PostGIS ST_Intersects giving unexpected results

I have a table of devices with a column last_known_coordinates (geography(Point,4326)). Here is an example last_known_coordinates: 0101000020E6100000569C00000D99EDBFE006C60B33F54640 Which is in ...
1
vote
0answers
42 views

Dissemination on a network

I'm looking to map the streams in which certain pollutants are found. These readings are points, and we assume that the water downstream of each point is contaminated. The mapping is not binary but ...
3
votes
0answers
70 views

Error using PostGIS query in MultipolygonZ type data [duplicate]

I am using this query in MultipolygonZ type data in my PostgreSQL database, but I got error for so many times the data is not rendered properly in PostgreSQL with PostGIS extension. PostGIS query:- ...
3
votes
2answers
63 views

How to identify "island" polygons? [closed]

I am curious how can I find all polygons that are touching with each other but are completely separate from the rest of the polygons is the same dataset, using PostGIS. For better understanding I ...
1
vote
0answers
18 views

How to transform XYZ Directory tiles to other tile format?

I am quite newbie to hosting / providing tiles. I have created my own tiles in XYZ directory format and my plan was to serve them through an URL like mywebsite.com/tiles/{z}/{x}/{y}.jpg However, I ...
3
votes
1answer
26 views

Intersecting polygons with raster in PostgreSQL using PostGIS and pg_raster

My goal is to use elevation data I loaded into PostgreSQL using pg_raster and intersecting it with a polygon. Within this polygon I would like to find the largest value in the pg_raster. My first ...
5
votes
1answer
76 views

Segment lines with incremental attributes - PostGIS

I have been trying to segment lines at specific interval (2000 m) using St_LineSubstring function. So far, I managed to accomplish that with the following code: SELECT ST_LineSubstring(the_geom, 2000....
2
votes
1answer
30 views

pgRouting: How to get other edge attributes

I have imported some OSM data in a PostGIS database (using osm2pgrouting) to use with pgRouting. I use Python to send the SQL queries. It works fine, however I noticed that the result includes many ...
3
votes
1answer
64 views

How to create mapnik XML file for database

I have a folder of sea maps in S57 format (i.e. file.000 files). And I want to create a .png at some location as input. So my tools are: old debian-based OS, S57 as map format, navi2pg or ogr2ogr to ...
1
vote
0answers
52 views

PGRouting: build path without shortcuts

I create a pathfinding algorithm using pgrouting library. This is the output I get with pgr_dijkstra: However, I do not need the shortcuts I'm having here. What are the possible approaches to make ...
3
votes
0answers
46 views

Making ellipses on a global data set

I am getting information on earthquakes that I am going to plot on a map. This consists of a calculated epicentre location and an uncertainty ellipse for the epicentre. This is a global data set, so ...
3
votes
1answer
36 views

ST_3DIntersection with LINESTRING M and POLYGON M

I have a database of aircraft positions and I'm trying to develop a way to recreate what airspace looked like at a time T. So my idea was to store each pair of positions as a LINESTRING M(lon1 lat1 t1,...

1
2 3 4 5
166