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
1answer
180 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 ...
0
votes
0answers
5 views

raster2pgsql - invalid pointer

I get the following error each time I run the raster2pgsql: [postgres@li1959-236 ~]$ raster2pgsql -I -C -s 4326 -M -F /home/temp/wrf_d02_202005010000.tif grib_test | psql -d cep Processing 1/1: /home/...
0
votes
1answer
63 views

GeoServer returns “schema does not exists” when creating SQLView Layer using REST API

I'm using GeoServer 2.16.2 (Kartoza/geoserver + postgis docker image) and I'm trying to create a SQLView layer using the REST interface. First I manually created the SQLView layer and copied the layer ...
0
votes
0answers
19 views

How to get intersection elements in PostGIS table correctly?

I am using the following PostGIS (2.4) query for getting intersection elements and it's intersection points. SELECT distinct ST_AsText((ST_Intersection(ST_Buffer(ST_ExteriorRing(a.geom), 0.2, 'endcap=...
1
vote
1answer
46 views

How to generate a column with a count of number of points in a polygon AND have 0 for the rows where there are no points in the polygon?

I have two data sets with a large number of points that I’m organizing into a hexbin grid. I'm associating these points with the hexbin polygons by using PostGIS to determine if a point falls within a ...
7
votes
2answers
438 views

Load and apply Style from Postgres in QGIS 3

I want to write a plugin in QGIS3 that changes the style of a vector layer. The styles to be loaded are saved in a postgres database in the table 'layer_styles'. I read here or here , to use ...
1
vote
2answers
107 views

Remove points where user was stationary

I have a table where points are stored with recorded time. I am storing points with 1s interval. Now if the user stayed approximately at the same spot for 2 hours, a number of useless points are ...
0
votes
2answers
250 views

Odbc_fdw.control is missing

I am using cartoDB and Postgres 9.3 including PostGIS. When I try to create development user in cartoDB I get this error: 'CREATE EXTENSION IF NOT EXISTS odbc_fdw SCHEMA public;' ERROR: could not ...
0
votes
1answer
50 views

Creating polygon GeoJSON from PostGIS and rendering in Leaflet

Drawing the polygons on QGIS and then import the layer directly on the PostgreSQL / PostGIS db. Then I create a JSON file via PHP and read it to the Leaflet. Everything works but zooming I see a lot ...
1
vote
1answer
270 views

Loading selected records in postgis raster table into QGIS using DB Manager

I've recently started using (out-of-db) raster data within postgis. The raster data is an output from a metocean forecast model, with new data available every hour. I'm uploading each new raster to ...
1
vote
1answer
78 views

Coordinates not consistently reversed (lon,lat interpreted as lat,lon) in Leaflet

I have a polygon layer fed from GeoDjango + PostGIS as GeoJSON. Its original CRS is 3908. It's being drawn as a GeoJSON layer in Leaflet in CRS 4326 (I figure it is transformed on the fly by Django ...
1
vote
1answer
12 views

ogr2ogr error loading kml file in postgis with php

So I have a kml file that I want to save to my postgis database, Currently I have the following code using php: $file = $request->file("item"); $name = time(); ...
0
votes
1answer
37 views

Enabling toggle editing when loading layers form postgis in QGIS

I'm trying to load layers from PostGIS using the code bellow: uri = QgsDataSourceUri() uri.setConnection(server, PORT, base, user, mdp) root = QgsProject.instance().layerTreeRoot() group = root....
3
votes
2answers
24 views

Inserting only subset of columns of shapefile to existing PostGIS table with different column names?

The situation So I have a shapefile with several columns: id|name_en|name_es|name_de|name_fr|... I also have an existing PostGIS table on my remote server: county_id|county_name|county_name_de|...
0
votes
2answers
348 views

PostGIS: Change raster values depending on values from other raster

I have two rasters and I want to change all the values from "raster1" to 4, where they are larger than the values from "raster2" and to 0, where they are smaller. So raster2 has only one value, which ...
19
votes
4answers
10k views

Adding GeoPandas Dataframe to PostGIS table?

I have a simple GeoPandas Dataframe: I would like to upload this GeoDataframe to a PostGIS table. I have a Database setup with the PostGIS extension already but can't seem to add this Dataframe as a ...
1
vote
0answers
32 views

Why is editting in QGIS producing invalid geometry in PostgreSQL database?

I am editing a boundary in QGIS(3.10.8-A Coruña) and facing an issue when writing the back to PostGIS (10.13). The workflow I am following is: Download data from PostGIS connection in QGIS Edit a ...
1
vote
1answer
1k views

Passing Feature ID into Variable using OpenLayers and GeoServer WMS

I have a WMS PostGIS-based polygon layer served by GeoServer, and whenever I click on any feature I want to get its gid value, so I can pass this value to the DOM. Unfortunately, I could not find a ...
0
votes
1answer
29 views

How to write route with measure to PostGIS with FME?

I have calibrated route in file geodatabase (ArcGIS). I have line coordinate (kilometre) in the start and the end point. I try export this route with M-value to PostGIS in FME. But in result I have ...
3
votes
1answer
773 views

Write Nominatim-like geocode query using only PostGIS functions?

I'm new to PostGIS and was anticipating being able to write geocode queries using only the functions provided by PostGIS. However, once I loaded my database using osm2pgsql and discovered tables like ...
1
vote
2answers
41 views

QField use main-detail tables from PostGIS/PostgreSQL

We are using QField for field data collection (forest survey). Our data is stored in a PostGIS/PostgreSQL database and we use QFieldSync to export and import data. This works fine as long, as we use ...
1
vote
1answer
74 views

ST_Polygonize/Shapely cannot polygonize when overlapping with shared node/point

I'm trying to use both polygonize_full (Shapely, python) and ST_Polygonize (postgis) to get all polygons from a set of line strings. There is one case where it fails: when there is an "overlapping ...
3
votes
2answers
1k views

How to UPDATE with LATERAL Nearest-Neighbour query?

Say I have table gps like: CREATE TABLE gps( gps_id serial primary key, measured_timestamp timestamp, geom Geometry(Point,4326), stop_id int --Null ); And I have a table of stops like: ...
1
vote
0answers
39 views

KNN with “Big Data”

I have a point dataset of over 30mill records and I am trying to select the closest 5 lines to each point. Reading this post there seems to be two methodologies Using <-> however as I want to ...
2
votes
0answers
26 views

When importing shapefile attributes (in a DBF) does the order of columns matter?

I'm working to import a batch of 250+ shapefiles into PostGIS. I'm using the shp2pgsql utility to load data. While doing some basic preparation I've realized that a few are missing 1-2 columns (...
0
votes
0answers
27 views

How to get the raster values of a point using Django's ORM (equivalent to ST_Value in PostGIS)?

I am working on a Django project that uses Django ORM to export raster data into PostGIS database. Here is the Django model: from django.contrib.gis.db import models class RasterWithName(models.Model):...
2
votes
1answer
721 views

Cache lookup failed for function 1 while enabling postgis on postgres 10

I am trying to enable PostGIS on a PostgreSQL 10 database on Windows. Earlier it used to work fine, but now I am getting an error 'cache lookup failed for function 1' on enabling the extension. I am ...
1
vote
1answer
176 views

Spatial queries with PostgreSQL on AWS

I have some data in PostgreSQL which I am exporting to Amazon RDS service. Is it possible to use PostGIS extension in AWS? (And will I need the mandatory geom column or I could run queries with only ...
0
votes
1answer
686 views

Django demands to set default. ValueError: String input unrecognized as WKT EWKT, and HEXEWKB

when I try to makemigrate models with Geometry fields to postgres DB I face with next message: You are trying to change the nullable field 'geom' on allotment to non-nullable without a default; we ...
3
votes
1answer
91 views

How to prepare PostGIS polygons for use in ArcGIS?

I've got a large dataset in PostGIS which I've made available to an indirect colleague who's using ArcGIS. However, he can't load most of the features which have the polygon geometry type. I've ...
4
votes
2answers
314 views

Fitting rectangle with maximum area inside polygon in PostGIS

I am looking to fit a rectangle inside a polygon which would have the maximum area for the rectangle. I tried using ST_OrientedEnvelope(geom) but this would give the rectangle fitted outside the ...
3
votes
1answer
183 views

T-rex tile server with postgis (windows) : utf-8 encoding error

I want to use T-rex as vector tileserver on Windows. I load osm.pbf file (from geofabrik) to PostGIs dataBase with osm2pgsql successfully. When I run the server with the following commands, I get the ...
0
votes
1answer
22 views

Runtime split our the line based on moving nearest vertex in pgrouting?

I attached my screenshot using pgrouting. Is it possible to runtime split our the line based on moving nearest vertex in pgrouting? Is there any easiest function or solution over there? Thank you
0
votes
0answers
23 views

shp2postgis changed field type and length

I'm using shp2postgis GUI tool load ESRI shapefile to PostGIS. In the shapefile, there is a field of type text which length is 2000, or even more. However after loaded in PostGIS, its type changed to ...
19
votes
3answers
6k views

How to calculate the angle at which two lines intersect in PostGIS?

I want to calculate the angle between two lines where they intersect in PostGIS. The starting point for angle calculations in PostGIS seems to be ST_Azimuth - but that takes points as input. My first ...
0
votes
1answer
33 views

How to set schema on GeoServer ImageMosaic JDBC?

I've publish raster from postgis to geoserver with ImageMosaic JDBC like explained on this post and it work properly on the default 'public' schema. But how to set a custom schema ?
1
vote
1answer
19 views

How to find features from edge_id in PostGIS topology?

I have created topology for a set of tables with PostGIS topology extension. Now, I want to get all linear features (which could be in different tables) connected to a particular line using a non-...
1
vote
3answers
49 views

Finding line segment of point on linestring using PostGIS

Is there an easy way to retrieve the line segment of a point on a linestring using PostGIS? To map a point onto a linestring, I use ST_(3D)ClosestPoint. Now I would like to find the index of the line ...
2
votes
1answer
57 views

Extracting a Point from MuItipoint and use it for a ST_DWithin query

I have a MultiPoint geometry column which has only one point (DB is a live server which cannot be changed now). I need to find all points within a certain distance from a fixed point I provide to the ...
5
votes
2answers
1k views

adding multiple points to a linestring in PostGIS

Though a newbie to PostGIS, I'm having trouble solving something that I think should be farily straightforward. I have two linestring tables, both consisting of simple, 2-node line objects: one has ...
10
votes
3answers
3k views

How to add vertices to existing linestrings?

If I have Linestring(1 2, 1 5, 1 9) and a Point(1 3) Is there any function that can merge linestring and point preserving the order so output would be: Linestring(1 2, 1 3, 1 5, 1 9)
1
vote
1answer
35 views

Find Closest Index in line string to insert new vertex using PostGIS

I want to find the index from Line String to insert new vertex on it. I am using ST_Add Point function but it requires specified index for inserting vertex.How to get those index based on given input ...
1
vote
1answer
322 views

ST_Clip not including pixels intersecting the border of a shapefile

I'm trying to clip a raster with a shapefile in postgis but want the clipped raster to include pixels that overlap the border of the shapefile which ST_Clip doesn't seem to be including. Here is a ...
0
votes
1answer
96 views

Publishing web map on website using PostGIS, GeoServer and OpenLayers

I have been trying to create a web map using PostgreSQL/PostGIS - GeoServer - OpenLayers. However, the resulting map is on my localhost and the data published in GeoServer can be accessed solely ...
2
votes
1answer
70 views

Importing .shp file into PostGIS with the function shp2pgsql

I am trying to import a .shp file into PostgreSQL, but so far no success. After completing the setup, I installed the PostGIS extension and finally went for some geo exploration. But, when I try to ...
0
votes
1answer
34 views

Best method for expanding Multi* geometries to their constituent objects [closed]

I have a Spatialite table with a geometry column containing a mix of multi and single geometries, e.g. (Multipolygon, Polygon) or (MultiLineString, LineString). The application that will be reading ...
0
votes
2answers
101 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)...
3
votes
2answers
173 views

Creating precise shapes using list of coordinates?

I am using st_concavehull to create boundaries/shapes. This works, however, the shape is not precisely tracing the coordinates. It is more puffy, as if trying to even out or "clean up" the actual ...
1
vote
1answer
41 views

Combining two queries to compare different columns

Suppose I have a table create table positions ( location geography, property1 int, property2 varchar ); I can do the following queries: select property2 from positions where st_dwithin(POINT, ...
1
vote
0answers
37 views

Uploading m/analysing raster vector data in PostGIS

I am trying to build a web-GIS platform where user can upload their data (raster & vector) and do some kind of analysis, like calculate zonal statistics. I read lots of articles and blogs about ...

1
2 3 4 5
152