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

Rendering 3D map in pgAdmin 4 to view Data? [on hold]

I found this error 3D geometrics not rendered in view data (pgAdmin 4). In GeoDjango I've updated my PointField model with dim=3. After successful migration, I cannot see my data in map. My Django ...
0
votes
1answer
14 views

Parse Error on PostgreSQL when using ST_AsText

I am new to using POSTGIS and PostgreSQL. I am trying to convert PostGIS coordinates into lat and long values. When I copy one of the values and query like SELECT ST_AsText('...
0
votes
1answer
15 views

Why are values inserted into postgis columns of type geometry in quotes?

I'm figuring out how to use the PostGIS extension, and I found a nice tutorial: http://postgis.net/workshops/postgis-intro/geometries.html. First the tutorial directs to create a table: CREATE TABLE ...
0
votes
0answers
17 views

QGIS reindexes data? [duplicate]

I work on QGIS. Whenever I make a layer (1500 lines) that is stored on a PostgreSQL server editable, a process of reindexing starts and lasts for long minutes! What is the reason and how could I get ...
0
votes
0answers
16 views

Detecting when vehicle has stopped [on hold]

I have live vehicle data coming to my postgres Table deviceId time(without zone) latitude longitude speed I am looking for query by which I can figure out when and where was vehicle stopped recently ...
0
votes
0answers
33 views

Import multiple PostGIS layers in QGIS map canvas via QTask QGIS 3

The vertex tool in red and the geometry displayed by the layer in yellow are not in sync from time import sleep from qgis.PyQt.QtSql import QSqlQuery, QSqlDatabase serverName = 'hostname' database = '...
0
votes
4answers
53 views

Add geometry information to a query

This query SELECT a.id, b.id AS id2, ST_MakeLine(a.geom, b.geom) FROM riverscale.points a, riverscale.points b generates this: I am guessing the necessary information for creating ...
0
votes
0answers
15 views

Joining inplace selected attributes of polygon layer to point layer using QGIS? [duplicate]

I am trying to join (selected) information of a polygon layer with multiple fields (e.g. CO2, NO2, PM10,...) to a point layer without creating a third layer. I know there is the Join Attributes by ...
3
votes
0answers
32 views

Computing driving directions in pgRouting/PostGIS?

I have calculated shortest route/path by using pg_Dijkstra algorithm in PostGIS and it is working fine. Now how to calculate driving directions from starting edge to end edge. I have gone through the ...
1
vote
2answers
89 views

Creating lines connecting each possible pair of points in PostGIS?

I am working with a group of points I would like to create lines connecting each possible pair of points. Example: If I have points A, B and C I would like to have lines connecting A-B, A-C and B-C. ...
1
vote
1answer
58 views

PostGIS servers to increase power

I am currently working locally on my terminal. I have Windows 10 x64, 12Go RAM, 428Go SSD, an Intel(R) Core(TM) i5-6200U CPU @ 2.30GHz, 2400 MHz, 2 hearts. I need to use PostGIS to make some queries ...
0
votes
2answers
55 views

Update a value by nearest polygon

I'm looking to be able to update a value by its nearest polygon. The below image shows polygons in green where the column roadclass = 'A' and shows polygons where column roadclass IS NULL in brown. ...
0
votes
1answer
42 views

PostGIS- convert points to polygon based on multiple shared attributes

I have a PostgreSQL PostGIS point table (not multi-points). I want to convert the points to polygons (using convex-hull?) grouping the points by shared attributes from 2 columns. In this example, '...
0
votes
0answers
29 views

Mapserver doesn't load geography columns of PostGIS

I have installed Mapserver 7.4.0 with PostGIS 2.4. However, I can't load any layer with geography column. This is my MAP file (with contents prior to LAYER omitted): MAP <omitted> LAYER ...
3
votes
1answer
70 views

PostGIS ST_Transform results shifted by about 25 metres

I have a road network downloaded from OSM and imported into PostGIS with pgrouting. I convert it with a specific local EPSG ST_Transform(geom, 26717), but the result is shifted about 20 metres to the ...
4
votes
1answer
79 views
+50

PGrouting number of results in pgr_dijkstraCostMatrix() not right

When running the pgr_dijkstraCostMatrix() on a PostGIS database with 41000 id's I get only 144000 rows in the matrix. I would have expected much more. What am I missing? I parsed the OSM data with ...
1
vote
1answer
34 views

Speeding up script to flag nodes within 100 feet of segments using PostGIS?

I'm working with two datasets, attempting to flag when an intersection is within 100 feet of a network subset. There are over 40,000 intersections and my network is ~ 1400 segments. The problem with ...
2
votes
1answer
38 views

Unclosed linestring to polygon works with QGIS but not with PostGIS

Just like the title, I am extracting OSM riverbank and a lot of riverbanks in OSM is in 'unclosed' linestring. This is the original river shape i extracted from OSM In order to polygonize the ...
0
votes
0answers
26 views

Spatial index for a raster column in a table in PostGIS

I have a raster table in PostGIS. When I run ST_SetValue(rast, geom , value) on it to change the pixel value at a point, it works very slowly. Do you know how can I create spatial index for a raster ...
1
vote
0answers
39 views

Does spatial indexing speed batch Point in Polygon

I have two tables, one with 66 thousand points and other with 5.5 thousand polygons. I'm trying to correlate which point is in which polygon and it is taking a long time. I want to know if spatial ...
1
vote
0answers
63 views

PostGIS: proper way to model object that follows sections of another object's path?

First, apologies if this has been asked before. I don't really know how/what to search for, and am struggling to just put this question into words. I'm trying to model aerial utilities in a PostGIS ...
0
votes
0answers
35 views

Transfer indexed Oracle geoloc table to PostgreSQL using ogr2ogr

I am copying tables using ogr2ogr from Oracle to PostGIS using ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME&...
1
vote
2answers
22 views

Load GeoTIFF band value into PostGIS

I am trying to upload a geoTIFF file into my PostGIS database. Using raster2psql, it works. raster2pgsql -I -C -M *.tif -F -t auto mytablename > mytable.sql psql -d mydatabase -f mytable.sql ...
1
vote
0answers
16 views

Oracle to PostgreSQL migration with ogr2ogr only imports first geoloc column

I transfer Oracle tables to PostGIS using Oracle Wallet and ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME>...
1
vote
1answer
46 views

Converting Cartesian to Spherical/Polar coordinates in PostgreSQL

I am trying to convert Cartesian to spherical coordinates in PostgreSQL. Illustrations: Input: x, y, z Output: elevation, azimuth I tried making a function by a code given in the link https://...
1
vote
0answers
25 views

Combine data of OSM roads into longer lines using PostGIS

For my vector map I'd love to display highways at a quite early zoom level. The problem is that this data would be quite big, so I thought I simply ST_Simplify it. The problem though is that the OSM ...
1
vote
0answers
33 views

Model for spatial distribution of resources in PostGIS? [closed]

I'm looking into a way to calculate the performance of each shop (funeral directors) based on resources available (funerals in last 5 years). Please see image attached, numbers are total funerals into ...
1
vote
1answer
28 views

How to set schema parameter in osm2pgsql calling

How I can to set schema name parameter when call osm2pgsql? For example: osm2pgsql --create -s -C 2500 -G -S D:\default.style -d osm_db -o input -U osm_user -W -H localhost -r pbf D:\osm.pbf I set ...
1
vote
0answers
17 views

Storing earth observation derived attributes for polygons [closed]

I want to generate a number of vegetation related indices (NDVI, NDTI, etc., min/max/avg/std (pixel values)) from earth observation data for a large number of polygons during the entire growing season,...
1
vote
1answer
25 views

Creating Isochrones - pgr_drivingDistance 2.6 SQL error

Im trying to create a matrix of driving distances from an osm2pgrouting parsed dataset. Following many blogs I have the below incorrect SQL: SELECT * from ways join ( SELECT * FROM ...
2
votes
1answer
150 views

Automatically add points geometry while GPS logging?

I have a PostgreSQL 10 Server (x64) running on my Windows 8 (x64 based) machine. On the top of my Postgres database, there is PostgREST API running that receives GPS data (5 seconds interval) from a ...
0
votes
1answer
95 views

Calculating the distance of each pixel in a raster from a point in PostGIS [closed]

I have a raster in PostGIS and a table of points. I want to calculate the distance of each pixel to each point. How can I do that? For example, if 9 pixels and 3 points, then I want to calculate 27 ...
2
votes
1answer
35 views

Converting {“type”:“MultiLineString”,“coordinates”:[[[x1,y1],[x2,y2]]]} to geography column using PostGIS?

I have a Postgresql table with a text column that has data like the below. {"type":"MultiLineString","coordinates":[[[-122.995882414,44.05766941700006],[-122.99818210099994,44.05768878400005]]]} I ...
1
vote
1answer
43 views

Allow client to upload ESRI shapefile in OpenLayers 3 [closed]

I want to build functionality to upload ESRI shapefile and overlay it on map using OpenLayers 3. Once user cross checked I want to save to PostgreSQL/PostGIS table. So far as I Googled I am not ...
1
vote
1answer
39 views

PostGIS mapfile [closed]

I am new to GIS and have been assigned a task to publish a map to the web. So I want to ask if anyone can give me a map file example that basically can: Load the OpenStreetMap and zoom to a area/...
-2
votes
1answer
40 views

Showing CSV-file in GeoServer [closed]

I created a PostGIS connection in QGIS and I added in a CSV-file. I added this database into GeoServer but inside this, I don't have the CSV-file. How can I add it as well? So is it not possible ...
1
vote
0answers
12 views

Read Postgis geom available as string variable with R

I have a Postgis Database from which I query data. I know that I can use the rgdal::readOGR() function with the PG driver to get geoms from the database into R. However, I am also querying lots of ...
0
votes
2answers
63 views

Trouble updating field calculatd by st_ functions

I had first run this code on several of my fields of my T1 table at the same time. UPDATE table_1 SET field_1 = liste.my_bool FROM( SELECT T1.id AS name, bool_or((ST_Contains(T2.geom, T1.geom)...
1
vote
0answers
31 views

Performant way to join 3D LiDAR mesh to 2D data

I'm new to 3D data and want to know what I should be searching for when I'm looking to find all the triangles in a 3D building mesh that fall within a 2D footprint. The mesh comes from LiDAR and ...
2
votes
0answers
34 views

Find orientation of rectangle in PostGIS for QGIS composer

I am storing rectangle in my DB in order to automatize the creation of technical maps of a networks. I am creating these maps in QGIS using the print composer. Many of these rectangles are oriented (...
1
vote
2answers
57 views

Create furthest point in multilineString

I am very new to PostGIS. I have a table with sections (multilineString) drawed on a map and a table with observations (point). Users can make observations and I have to automatically place the ...
1
vote
0answers
33 views

Trigger on PostGIS line layer occurs twice [closed]

Using this tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, ...
2
votes
0answers
24 views

Using a PostGIS raster catalog in GeoServer? [closed]

I have a PostGIS table cataloging 10,000+ GeoTIFFs. Each table row has a name, file path, and a polygon as the bounding box. The rasters are spread out all over the globe, they are not adjacent. I ...
1
vote
1answer
27 views

Combining st_snaptogrid with st_centroid and group by using PostGIS?

I have a table of devices, each of them has a field 'location' that is a postGis geometry(Point,4326) I need to operate a GROUP BY based of the placement on a grid, I'd use ST_SnapToGrid and then I ...
0
votes
1answer
42 views

Creating minimum spanning tree using OpenStreetMap and QGIS?

I'm working in QGIS (3.8) and PostGIS (2.5) and I have 2 layers: 1. set of points, 2. set of OSM roads. I would like to generate a subset of the roads network which represents the minimum spanning ...
1
vote
3answers
45 views

PostgreSQL/PostGIS error “pseudo-type record”

I've got, in PostgreSQL/PostGIS, create table datos ( gbifID int primary key, orden varchar (50), eventDate varchar (50), geom geometry); When I run select distinct (geom,eventdate), geom from ...
1
vote
0answers
21 views

Solid is invalid : PolyhedralSurface (shell) 0 is invalid: self intersects

I am trying to calculate the volume of an object with geometry type --PolyhedralSurface. ST_MakeSolid(geom) is valid, while ST_Volume(ST_MakeSolid(geom)) is invalid for the following reasons: ...
1
vote
1answer
26 views

ERROR: function st_issolid(geometry) does not exist

My goal is to calculate the volume of a room whose geometry is POLYHEDRALSURFACE. First, I tried to use "ST_Volume" on my geom directly and got the following error "ERROR: function st_volume(...
1
vote
0answers
40 views

Calculating latitude and longitude fields using PostGIS? [duplicate]

I have a shape in PostGIS that has 2,658,061 records (points). I need to create a field that is called "Latitude" and another that is called "longitude" and calculate a position in WGS84 (SRID: 4326)...
1
vote
1answer
22 views

Migrating SDO_JOIN to PostGIS

I have a query in Oracle to select polygons from my_polygon_table that interact or reside inside of a single polygon residing in border_table: Select t1.*, t2.*, sdo_geom.sdo_intersection(t1.geoloc, ...