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
4
votes
2answers
322 views

Saving QGIS layer to PostGIS using PyQGIS?

How to save the memory layer from QGIS in PostGIS database in specific schema (public) using PyQGIS or python? Here some example from my code : outputs_QGISDIFFERENCE_1=processing.runalg('qgis:...
0
votes
0answers
6 views

Importing Esri File GDB into PostGIS error:Geometry type (MultiCurve) does not match column type (MultiLineString)

I want to import an Esri file geodatabase to PostGIS 2.2 (PostgreSQL 9.4) using or2ogr. I am getting the following error. ogr2ogr -f "PostgreSQL" PG:"host=localhost user=username dbname=mydb password=...
0
votes
0answers
14 views

Problems with ST_ClusterDBSCAN cluster sizes

I want to cluster all villages (and towns, ...) around the world. So instead of having millions of them, I'd like to reduce them by combining villages that are close enough (like 10km or so) to each ...
0
votes
2answers
35 views

Determine If PostGIS Geometry Is a Circle

I have a PostGIS database table that contains several generic geometries in one of it's columns. Sometimes I have geometries that are intended to be circles which I insert into the database as the ...
0
votes
1answer
15 views

Optmizing PostGIS minimum distance query

I have the field_1 of my Table_1 that must have the minimum distance between the geometry of my Table_1 and any geometry of my Table_2. To do so, I used a CROSS JOIN (Full Cartesian Product) between ...
6
votes
3answers
3k views

PostGIS : Does the <-> operator work for Geography?

With the recent PostgreSQL/PostGIS version, you can now use the <-> operator to find the nearest rows to a point: Indexed Nearest Neighbour Search in PostGIS According to my tests, this query:...
1
vote
0answers
18 views

Create curve of target distance

I'm trying to create a curve between two points in PostGIS that meets some target distance. So my input is point_a, point_b, target_distance, and my desired output is a curve between those two points ...
2
votes
1answer
100 views

Multiple shortest paths with pgRouting using a list as a secondary table

I have been using a postgis database with pgRouting to solve shortest path problems. I have been using a query like: select seq, e.geom, pd.cost, pd.id1 as node from pgr_dijkstra( 'select gid as id, ...
2
votes
0answers
63 views
+50

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
1answer
65 views

Turning a single MultiPolygon into many small polygons?

I have a large GeoJSON file with a single very complex MultiPolygon, and I would like to turn it into an identical file with many small polygons. How can I do this? I can use ogr2ogr or mapshaper or ...
1
vote
1answer
54 views

Correlate two different zone system records

I am working with two data sets from different sources that represent the same phenomena and have a challenging point. Both data sets represent demographic attributed of the population [ person id, ...
1
vote
1answer
27 views

How to get all the sub-rectangles (subgeohashes) of a given geohash

I am trying to use geohash to generate a grid of rectangles to cover an area. But I am not very familiar with the coding system of geohash. My question is: If I have a specific geohash gh of 3 ...
1
vote
1answer
29 views

Accelerate neighbor analysis in QGIS PostgreSQL / PostGIS

For a project I have to show the relationship between 2 or more objects. For this I use the code below in QGIS in step 1 I determine the relation in step 2 the items are counted. In the test set I use ...
0
votes
2answers
51 views

Geographic coordinates in PostGIS to QGIS and shapefile layers

I've got in PostgreSQL/PostGIS the table "points" with latitude and longitude fields. I've done update points set geom = st_point (longitude, latitude); and also select updategeometrysrid ('points'...
1
vote
4answers
75 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 CROSS JOIN riverscale.points b WHERE a.id != b.id; generates this: I am guessing the ...
0
votes
1answer
46 views

Editing PostGIS multipoint table with QGIS

I created an empty multipoint table in PostGIS and I want to fill it with hand-placed points in QGIS. I have connected them and checked that QGIS identifies the new layer as multipoint. I've got a ...
0
votes
0answers
43 views

DB Manager in QGIS 3.8 Zanzibar unable to load from PostGIS

I am no longer able to load tables from PostGIS (PostgreSQL 9.6) since the last update of QGIS. On polygon tables, the DB manager now shows a ? icon, point icon, line icon and polygon icon for each ...
1
vote
2answers
44 views

Distance between two point layers

I have a main addresses point layer and a areas point layer. Both layers have XYZ coordinates in the attribute table. I need to create a table based from the addresses layer which calculates the 3D ...
0
votes
0answers
23 views

PostGIS Out-DB Rasters - Segmentation Fault

I am loading data from a NETCDF file with raster2pgsql, which I would like to keep as an out-DB raster. The command I use is (note the -R option): raster2pgsql -s 96482 -I -d -F -R NETCDF:"/home/...
1
vote
1answer
92 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 ...
1
vote
1answer
19 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
38 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 ...
7
votes
2answers
520 views

Identifying points on left or right side of street using PostGIS?

The scenario: I have points (addresses) and multiline strings (streets) i.e., ap and street tables in my PostgreSQL 9.5 db. Table ap contains 3 rows (sample points) while street contains 85 rows. The ...
7
votes
2answers
2k views

Deleting small gaps (slivers) between polygons?

I try to delete the slivers that appeared when I have merged some polygons. I have two different cases: For the CASE 1, no problem I fill the gap with: CREATE OR REPLACE FUNCTION sliver_killer(...
1
vote
0answers
34 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
257 views

How to select all points inside a polygon in PostGIS?

I have a table samplecol that contains (a sample): vessel_hash | status | station | speed | latitude | longitude | course | heading | timestamp | the_geom ...
2
votes
2answers
520 views

Does ST_Intersection work in 3D?

I am using "ST_Intersection" between two 3D lines which I believe do not intersect, but the result gives me their intersection. SELECT ST_ASText(ST_Intersection( ST_GeomFromText('LINESTRING( ...
0
votes
1answer
22 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('...
1
vote
0answers
47 views

Migration in PostgreSQL 11 from PostGIS 2.4 to 2.5 fails

This is my attempt to migrate the data: ALTER EXTENSION postgis UPDATE; WARNING: 'postgis.backend' is already set and cannot be changed until you reconnect ERROR: operator does not exist: gidx ...
5
votes
2answers
2k views

Finding points along a path

I am unsure of the best approach to this problem. I can think of some ways of doing it which I will list below but I am looking for the best and most efficient way of doing this. Problem Given a ...
0
votes
0answers
20 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
41 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
0answers
19 views

Detecting when vehicle has stopped [closed]

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
16 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 ...
1
vote
1answer
61 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 ...
4
votes
1answer
95 views

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 ...
3
votes
1answer
698 views

Replacing pixel value by lookup table using PostGIS?

I was wondering if it is possible to write a PostgreSQL statement to replace the value of each pixel in an input PostGIS-raster with a value obtained from a look up table. For example, suppose I have ...
1
vote
2answers
106 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
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 ...
3
votes
2answers
5k views

PostgreSQL/PostGIS: create spatial view

I have a polygon layer and a point layer. 1) I wrote a spatial query to count how many points fall in each polygon: SELECT polR.codeR, count(pntAZ.geom) as tot_pntAZ FROM boundaryR as polR LEFT ...
0
votes
2answers
61 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. ...
4
votes
5answers
3k views

QGIS DB Manager - Load new layer not loading SQL query

I am trying to load a set of queried data into the map window but the layer will not load. I have a query that has successfully run returning data from two tables. When I select to 'Load as new ...
7
votes
1answer
606 views

Using QField with Multi-User Access also Offline?

I got 10-20 QGIS-Users which edit all one Project online and offline. Sometimes they do it at the same time. Until now we only used QGisDesktop with a PostGis Database stored in QGIS Cloud(50MB ...
2
votes
2answers
50 views

Spatial DB lookup to image DB

I am working on a library project for rectifying old map imaging on a map. The images are in tiff and jpeg formats. After rectifying the images I want to save the geographical information in a ...
1
vote
1answer
35 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 ...
0
votes
1answer
46 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, '...
1
vote
1answer
48 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://...
0
votes
0answers
30 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
2k views

Forming queries with node.js, PostGIS and GeoServer?

I am not sure If I got the overall web-mapping architecture right. I can easily find tutorials about each part, but nothing on how to connect them. I have openlayers in the front-end, node.js as a ...
4
votes
4answers
4k views

Querying points inside and outside of polygons?

In PostGIS I have two tables: Table that contains polygons: table a.provinces gid | geom | name | ------------------------------ 1 | 010604 ... | Champagne | Table that contains ...