The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
17 views

OpenStreetMap displaying/rendering optimization [on hold]

It seems that soon I could be working on one River Information System (the inland waterway navigation and management). The system should be upgraded with new functionalities and web frontend should be ...
0
votes
0answers
13 views

Converting geographic data from and to 3D data in PostGIS?

Is there was a way to convert a geography value to a 3D geometry value (in ECEF, for example), and vice versa? I could probably create a function to do this myself, but is there already a function to ...
1
vote
1answer
20 views

Update a field from a PostgreSQL point table (wherever it's NULL) with certain field value from closest polygon?

Given a point table (table1) and a polygon table (table2) in PostgreSQL, I am trying to update a field from table1 wherever its value is NULL. This field should be updated with a certain field value (...
0
votes
1answer
25 views

Storing and accssing regular lat-lon matrices in PostGIS [on hold]

I'm quite new to PostGIS databases, and have kind of a general question. I have sets of regularly spaced lat-lon grid data (grid size about 2000x1500) and one float value per grid point, that I would ...
1
vote
1answer
39 views

PostgreSQL create line from points

How do i create a linestring from points sort by date in PostgreSQL? I tried: SELECT observations.id, ST_MakeLine(observations.geom ORDER BY observations.date) AS newgeom FROM observations ...
4
votes
1answer
29 views

Spatial index not used in view which union several tables

I have multiple spatial tables each of which I have created gist index, and query for each table will cost 10-200ms as shown in the following example, the spatial index can be used. Example 1(with ...
2
votes
3answers
41 views

PostgreSQL - attach to point table the maxium distance value to edge of containing polygon

I'm trying to find the furthest distance from a point to the edge of the polygon it sits in. The polygons in this case have been converted to vertex points. I have two POINT tables in Postgres (and a ...
2
votes
1answer
31 views

ST_MakeLine not returning proper latitude/longitude values

I have a table consisting of latitude, longitude and geom values: index | longitude | latitude | geom | cluster_id ----...
-2
votes
0answers
27 views

Geopackage in Postgresql [closed]

I am a beginner to learn Postgis, Postgresql and Geopackage. I have read a lot of documents on the web about them, but it's specialized. Can everyone explain help me what are they? and how are they ...
0
votes
1answer
38 views

Tally of sequences of points within point-specific buffer, rolling over timestamps, in PostGIS?

In a given string of points, I've been able to designate buffers for each point in the string (i.e. a string of points are relocations over time). However, I'd like to be able to then summarize how ...
1
vote
2answers
81 views

Find points within polygon with PostGIS really slow

From some millions of points I am trying to select those points that are within a polygon and save them into a new table. Both datasets with spatial index and both on projected coordinates. The query ...
0
votes
1answer
51 views

QGIS can't connect to PostGIS but pgAdmin4 works

I have a PostGIS DB at a remote server and I can connect to it from my desktop using pgAdmin4. However, when I try to connect though QGIS (v3.4) I get an error: FATAL: no pg_hba.conf entry for host "...
2
votes
0answers
32 views

Getting distance between point and line with specific values?

I have a table named "housenumbers" that contains points with addresses fields, and one of them is "street_name". And I also have another table named "streets", that contains lines that are the ...
1
vote
1answer
41 views

Trigger spatial join on insert and update

I've a problem with a trigger in PostgreSQL. I can do the query but I don't know to develop a trigger. The example query is: select schemaA.table1.value from schemaB.table2 on st_contains(schemaB....
2
votes
1answer
38 views

Find closest polygon from point and get its attributes?

Given a set of points and a set of polygons, I am trying to find for each point what the closest polygon is and bring over the attributes from that polygon to the point (together with the actual ...
1
vote
0answers
25 views

Move points to the main façade

How can I move the points to the main façade using Postgis? I have used the following query SELECT ST_ClosestPoint(ln.geom, pt.geom) FROM "testes"."line1" AS ln WHERE ((pt.geom <-> ln.geom)...
2
votes
3answers
54 views

Use variables in PostgreSQL/PostGIS code

I'd like to create an SQL file, where the user can just change variables on the beginning , and use it in different configuration system. Typically for load layer from different PostGIS server with ...
0
votes
0answers
23 views

pgrouting at unsplitted roads

I have a railroad network and want to use shortest path algorithm on that. Unfortunately I have a problem at this intersection, where I want to have the path coming over edge 14692 to 14620 and ...
0
votes
0answers
11 views

PcPointcloud to PcPatch

In a table I have the columns x, y, z, the following code turns these into PcPoints, how do I within PostGIS turn these into a PCPatch, preferably in groups, the following code returns an empty table. ...
1
vote
1answer
24 views

ST_ClosestPoint() moves all points to one line instead of closest line

I have a line network and I've created evenly-spaced points along those lines. The problem is that the points are not exactly on the line so subsequent overlay operations will not work as expected. I'...
1
vote
0answers
60 views

PostGIS points along a line aren't actually falling on the line

I have a line network and I am placing points at equal intervals along each line. These points will be used to cut the lines into segments. I have found a way to create the points along the lines: ...
1
vote
1answer
27 views

Transfer spatial data between databases using pgAdmin4

I'm new to pgAdmin4 and performing tests before jumping in the deep end. I've uploaded a large dataset into a the public schema within a database named 'spatial_db'. Rather than re-uploading the data ...
1
vote
0answers
25 views

Inserting xyz as pointcloud points

I'm trying to insert some xyz point data into a pc_point, I get the error ERROR: array dimensions do not match schema dimensions of pcid = 1 running SELECT PC_MakePoint(1, ARRAY[-127, 45, 124.0, 4....
1
vote
1answer
36 views

Using Select statement inside of array constructor in PostgresSQL?

I'm working with point coordinates in PostGIS and I have a problem. My goal is to generate linestrings by using ST_MakeLine geometry constructor (see snippet 1). Source data is pre-generated ...
3
votes
1answer
54 views

Obtain best-fit bearing for sequence of points

I'd like to calculate the overall bearing (direction) of a sequence of points in PostGIS. It's clear how to do this for two points, using ST_Azimuth. How can I do this for the best-fit line through a ...
2
votes
2answers
76 views

How to get distance between a point and a list of highways?

I'm new to GIS. I'm trying to determine whether a list of GPS points are within a highway/road (in terms of meters). I have downloaded a OSM file and populated my database using osm2pgsql. I have the ...
1
vote
0answers
27 views

is there a way find all coordinates inside a polygon and use each points to get their value (radiance)

I have this GeoTIFF file that I acquired in NOAA ( Tarball contains the TIFF file VIIRS ) that has a light intensity in each area in the world. Its categorize per tile. I used the one that covers ...
1
vote
0answers
21 views

Count points that are within certain radius within a polygon

I have 4 table, I want to count police that are within 5km of a point (dam), schools and clinics within 3km same dam. So far I do get the results if I'm focusing on one table $str = pg_query($conn, "...
1
vote
2answers
49 views

Using table joins with ST_DWithin to optimise process?

I am trying to select all segments (among millions of them) that are less than X metres from a few thousands polygons obtained from a raster dataset (meaning they've got quite a complex geometry - ...
1
vote
1answer
34 views

Speedup function to calculate distance from centroid of building to river segment in PostgreSQL

I am trying to measure the distance from the centroid of a building to a river segment using the following function. CREATE OR REPLACE FUNCTION river_foo_mapper () RETURNS trigger LANGUAGE plpgsql AS ...
1
vote
1answer
11 views

GeoJSON from PostGIS view using PDO

I want to use PostGIS data to display it in Leaflet. I've created a PostGIS view (v_baeume_GeoJSON) that delivers already valid GeoJSON. When I retrieve the data from this view using PDO I have to do ...
2
votes
1answer
324 views

Installing PostGIS on PostgreSQL 12

I installed PostgreSQL 12 using Windows Installer. After the installation, there´s no option to install postgis extension. I tried to add postgis support in a database using CREATING EXTENSION and I ...
4
votes
1answer
39 views

Aggregating multiple rasters within a polygon in PostGIS

I have several raster tables in PostGIS (four in fact but in this case let us assume there are just two) - same cell sizes, same alignment but with different types of value (e.g 3d_pot, 3d_area). ...
2
votes
1answer
55 views

Why is a “geography” type needed at all in PostGIS? [closed]

Let a and b be two points. ST_Distance(a, b) returns their distance. Instead of having two data types, geometry and geography, that practically overload the meaning of ST_Distance(), we could instead ...
2
votes
1answer
33 views

pgr_createTopology uses too much memory and “connection to the server has been lost”

I have the large roads network (urban walking paths). I'm trying create topology in my database but when query reaches 175 thousands of edges, begins to increase memory usage. In the same time CPU ...
3
votes
1answer
48 views

ST_Buffer return table without columns

When running my buffer over my points, I am losing all columns into my table. Here is the query: SELECT st_buffer(geom, 100) INTO gis_osm_pois_buf FROM gis_osm_pois; Is there any way to keep my ...
1
vote
0answers
55 views

PostGIS ST_Difference is returning overlapped areas

I´m using this SQL sentence to obtain the difference of two layers, exactly I understood that ST_Difference does, that is, it should return polygons that don´t overlap and the not overlapped area when ...
2
votes
0answers
27 views

Calculate real surface area with PostGIS

I would like to know if its possible to calculate a real surface area (with DEM information) only with PostGis function ? I know it's possible with Saga and Grass.
1
vote
0answers
35 views

Editing PostgreSQL/PostGIS table via join in QGIS

I have a standard PostgreSQL/PostGIS table and another table which stores a geometry. They both already have a relationship with ondelete/onupdate cascade. I make a PostGIS connection in QGIS 2.18, I ...
1
vote
0answers
20 views

PostGIS - Measuring the angle at the touching point of two lines

I want to calculate the angle of two lines of a routing network exactly at the "touching point" (the topological vertex). My previous attempt was using the ST_ANGLE() function of PostGIS, which gave ...
0
votes
2answers
67 views

What is the full syntax SQL reference for the `geometry` data type?

I have a database in which a field has the data type geometry(Geometry, 4326). In another similar database a field merely had the data type geometry. Where can I find the full reference on this data ...
0
votes
0answers
25 views

PostGIS (or rpostgis) issue on Ubuntu 18.04

I've installed PostgreSQL 10 and PostGIS on Ubuntu following instructions here. Now I'm migrating an R interface from Windows to said Linux box and have an inconsistency I can't sort out. When I ...
1
vote
0answers
33 views

Turn 3D multipolygon with missing faces into cube

I have data of buildings that are missing walls, like this MULTIPOLYGON(((5 5 0, 5 0 0, 0 0 0, 0 5 0, 5 5 0),(5 5 5, 5 0 5, 0 0 5, 0 5 5, 5 5 5))) This would be the floor and roof, stretching from 0 ...
1
vote
0answers
14 views

Extracting relevant data from CAD format into a PostgreSQL database

I have received some survey information in the format of a .DWG (AutoCAD file) and I am trying to extract the data from this file into QGIS and then into a PostgreSQL database. In the .DWG file there ...
1
vote
0answers
32 views

How To Display ST_AsMVT tiles in Leaflet

I have a large polygon dataset in PostGIS I would like to display in Leaflet map. I understand st_asmvt() query will return mapbox vector tiles. I can successfully query the data in postgis but I ...
1
vote
0answers
42 views

raster2pgsql fail with JPEG encoded image

The problem I have one JPEG encoded raster that works in GDAL and QGIS. The original file 05-D Braga.jpg is available online. The CRS is EPSG:20790. To upload it to Postgis, I use: raster2pgsql -s ...
3
votes
1answer
48 views

Unusable QGIS layer from PostGIS

I am developing a QGIS plugin. I need to load some polygons from a table in PostGIS. This table is defined with this SQL query : CREATE TABLE cad.polygon( id_polygon serial NOT NULL, geometry ...
2
votes
0answers
50 views

Find polygons which are intersecting / touches with defined polygon [closed]

I have a polygon and I would like to find all polygons from the table planet_osm_polygon which my polygon lies in or touches. I tried to use st_intersects function but it doesn't work as I would ...
0
votes
0answers
23 views

How work with PostGIS in Symfony 4 and Doctrine 2

I am looking to work with PostGIS in Symfony/Doctrine. My search returns this old post: https://stackoverflow.com/questions/11713508/how-do-i-use-doctrine2-to-work-with-geospatial-queries-using-...
4
votes
1answer
44 views

Using PostgreSQL NOTIFY payload in QGIS

I am using the NOTIFY signal of PostgreSQL intensively to update a PostGIS-layer in the map (see https://oslandia.com/en/2017/10/07/refresh-your-maps-from-postgresql/). Additionally to the channel '...