Questions tagged [postgis]

2

votes
0

answer
19

Views

What's the most optimal query in PgSql to find the nearest neighbor in a huge dataset?

I have a huge table (about 40 Millions rows) called nearest_spot representing lines (in the linestring format) and the closest spot they're to (there is about 1500 different spot, stored in another table). The nearest_spot table comes like this : data_id || spot_id || spot_name || link_geom Where d...
Adrien
1

votes
1

answer
684

Views

How to use postgis type Geography with spring-boot and postgresql?

I have a Spring-boot project with hibernate. Also, I have a postgresql database with postgis extension. I really want to make use of the postgis extension geography type. Unfortunately, it seems that Hibernate does not support the geography type, only type geometry, which is unsufficient for my need...
Ville Miekk-oja
1

votes
2

answer
970

Views

Using files generated by other recipes in Yocto

Note: The general question is in bold at the end of this post. I'm trying to build PostGIS 2.2.7 with Yocto (Rocko) for my Linux i.MX6 based embedded system. First of all, I have installed PostgreSQL 9.4.15 from OpenEmbedded Layers (https://layers.openembedded.org/layerindex/recipe/5558/) and all th...
danlor
0

votes
0

answer
4

Views

I receive a parse error when I try to move geometry data from one column to another in PostGIS

I combined two geometry columns into one using the following code: UPDATE intxsdup SET geom = ST_GeomFromText(firstpoint, 26917); INSERT INTO intxsdup (geom) SELECT ST_GeomFromText(lastpoint, 26917) FROM intxsdup; I then added a serial 'bid' column and made it the primary key Then, I tried to move t...
Beth Bowers
1

votes
2

answer
48

Views

Making a ST_Distance query scalable under high loads?

I'm running a database server that contains a large table of business addresses. An app is connected to it that takes in user co-ordinates and the type of business they are looking for and returns the business nearest to that user. Originally, my app used a simple SELECT statement to find the neares...
GreenGodot
1

votes
1

answer
50

Views

Calculate distance on a polyline of a road between 2 lat/lons

This is not distance as the crow flies. I'm looking for an API like this: distanceMiles = calculateMilesBetweenPointsAlongRoad(LatLon1, LatLon2, RoadPolyline) I have a road represented as a polyline. As a vehicle moves on this road, I capture lat/lons. I want to calculate the distance the vehicle tr...
Jason
0

votes
0

answer
3

Views

Count multiple point layers in polygon

I am trying to count different stores inside trade zone polygons in a sql-query. Each store is a different table in my postgis database. It is possible to count points in polygons using the following code taken from https://gis.stackexchange.com/a/54680/19349: SELECT grid.gid, count(kioskdhd3.geom...
Kempie
1

votes
2

answer
50

Views

How to accurately store geolocation data, filter within a radius, and calculate distance?

I write new locations with: ST_SetSrid(ST_MakePoint(:longitude, :latitude), 4326) They get saved under 'profile'.location: SRID=4326;POINT(-75.1234 35.1234) I try to filter profiles within some :radius in meters where :ownLocation is some location string that consists of a bunch of numbers: ST_DWith...
atkayla
1

votes
0

answer
406

Views

PostGIS st_intersects() and spatial index

I have huge troubles understanding the usage of indexes by PosrgreSQL/PostGIS. I have two spatial datasets: building outlines and flood polygons. There are 4.700 flood polygons that are quite complex (many vertices) and 1.100.000 building outlines. I import them into the database using QGIS. The ge...
JerT
1

votes
0

answer
45

Views

When create a new Persistence Unit?

I'm developing a GEO based app using PostGIS and Hibernate and I'm stuck on persistence units. I have entities with and without GIS attributes. And my persistence.xml is this: java:jboss/datasources/PostgresDS entity.PersistentEntity.Account entity.PersistentEntity.Score java:jboss/datasources/Postg...
GabrielRado
1

votes
1

answer
23

Views

postgresql call function with wrong params

I declared postgresql function as follows, this function simply calculate distance between 2 points in kmeter CREATE OR REPLACE FUNCTION show_places(ref refcursor,lat float8,lon float8, radius double precision) RETURNS refcursor AS $$ BEGIN OPEN ref FOR SELECT * FROM place p where ST_Distance(ST_Poi...
Java Player
1

votes
1

answer
145

Views

find polygon which contains the point using postgis/postgres

I have 2 tables and both of them contain 2 columns. The first table contains 50 million rows while 2nd table contains 50 rows Table1 - Columns: point_coord (datatype: point), id (integer) Table2 - columns: region (datatype: geometry - they are polygons), id (integer) The id column in the 1st table i...
Aman
1

votes
1

answer
148

Views

select radius from ST_MinimumBoundingRadius

I want to get just radius value from ST_MinimumBoungingRadius. Something like this (from postgresql documentation) works just fine: SELECT radius FROM ST_MinimumBoundingRadius('MULTIPOINT(1 2,3 8,5 6)') So I don't understand, why doesn't work similar query on existing table: SELECT radius FROM (S...
Tomas
1

votes
0

answer
976

Views

PostgreSQL out of memory: Linux OOM killer

I am having issues with a large query, that I expect to rely on wrong configs of my postgresql.config. My setup is PostgreSQL 9.6 on Ubuntu 17.10 with 32GB RAM and 3TB HDD. The query is running pgr_dijkstraCost to create an OD-Matrix of ~10.000 points in a network of 25.000 links. Resulting table...
PaDe
1

votes
0

answer
250

Views

JPA Criteria Query: SQL function call parameters from SubQuery

I'm trying to construct this Criteria Query (PostGIS function): Create a subquery from getting the st_union (aggregate) from polygon selection. Compare a point from union of polygon by checking if it is st_within that polygon. PostGIS Query (works): SELECT place0_.id FROM place place0_ WHERE st_with...
Catalino Comision
1

votes
0

answer
100

Views

PostGIS not working properly on Heroku

Added postGIS to heroku database and checked if it is installed. But it does not recognize rgeo and postgis methods. My coordinates are type :geometry. Local is all fine but on production WKT format is not recognized. Example: In development returns: 'POINT (45.815927 16.00538)' In production retu...
code monkey
1

votes
1

answer
127

Views

How do I plot PostGIS geometries using geom_sf?

Data source is PostgreSQL database with PostGIS geometry types. I have no problem querying the data I want directly from SQL into a data.frame using the RPostgreSQL package. Since R complains about raw geom types, I use ST_AsText() on the geoms in the SQL query so that they are stored as character...
Brian D
1

votes
0

answer
39

Views

Generate geometries from each intersection and apply aggregation to row

I'm trying to figure out how to use PostGIS (since I have my polygons stored there already) to generate a set of nonoverlapping polygons. The source table has two columns (geometry and occurences), geometry is always a valid polygon, occurences is an integer. The polygons in the table can overlap. F...
vvondra
1

votes
0

answer
140

Views

Solving Gaps and Islands for Time-Series data with additional Constraints (PostgreSQL)

I think this is a Gaps and Islands problem however my gaps are actually defined by logic and constraints rather than missing data. I am also working with time-series style data rather than integer IDs so many of the examples I've found don't seem to apply - or I don't know how. I am using PostgreSQL...
Alex
1

votes
2

answer
45

Views

Postgres | how to combine from two or more table into single table

i have lots of data which is divided into two or more tables.Then how to fetch the data from all the table. First table is point table which is divided into three parts first table = ab_p1_point second table = ab_p2_point third table = ab_p3_point Another table is line table which is again divided...
Nawaz Sharif
1

votes
0

answer
133

Views

how to upload grib file on postgis

I'd like to upload grib files on postgis. I've found this, this and this about this topic. These solutions seem to work only with a grib2 format as the code I tried runs but I had an empty table on my postgis database. There is a way to upload a grib file on postgis without converting it on a gri...
Glori P.
1

votes
0

answer
31

Views

Postgresql: subquery behaves differently in main query is INSERT or DELETE

I am running quite a complicated query on temporary tables in Postgresql 10 + Postgis. I have this query : WITH isolatedPonctualBuildings AS ( -- sélection des bâtiments ponctuels qui n'ont pas de recoupement avec un bâtiment vectoriel SELECT DISTINCT bat.pkid , bat.pkid_emprise, bat.origine ,...
Darth Kangooroo
1

votes
1

answer
76

Views

Backup complete postgis database with geometry transformation

I have a PostGIS enabled PostgreSQL database that is no longer needed in production. I would like to back it up, but the geometry columns of PostGIS should be transformed to a simple, long-term stable text format like WKT. I'm aware of the ST_AsText function. SELECT road_id, ST_AsText(road_geom) AS...
nevrome
1

votes
0

answer
43

Views

Postgresql trigger to calculate Multigeometry

Based on the following solution Postgresql trigger to calculate total score I am trying to create a trigger which will update an existing record's geometry in cat_projects by adding multipolygon from a cat_projects_geom table where name=name. Cat projects_geom contains multiple polygons with same Na...
Tamas Kosa
1

votes
0

answer
95

Views

Running PostGIS with Django on Heroku

Trying to run a GeoDjango app on Heroku and it's being a really piece of work. After struggling through a variety of problems, I've come to one I can't seem to hack my way out of. File '/app/.heroku/python/lib/python3.6/site-packages/django/db/models/fields/__init__.py', line 673, in db_parameters...
Sillson
1

votes
0

answer
96

Views

Symfony 3.4 Global Variable

I am developping a platform of webmapping, It's about geolocalised data of the mediterranean sea (e.g Salinity, pH, pCO2 etc...) took from a boat that travels between Tunis, Marseille and Genova. The names of the parameters (salinity, pH...) retrieved from the sea can change according to dictionarie...
Salim Ben Aissa
1

votes
0

answer
217

Views

Integration of PostGIS with JHipster

We have currently a rather convoluted way of using the Geometry fieldtype of PostGIS with JHipster (4.6, currently trying to upgrade to 4.14 - which is not going smoothly - will probably post another question for this) we added hibernate-spatial and liquibase-spatial to pom.xml then create the jdl E...
Nenad
1

votes
0

answer
163

Views

Flask uploading shapefiles into postgis

I am trying to upload a shapefile into PostGIS. I am using Flask and have gotten the files from the user (.shp, .shx, .dbf and .prj) and saved them to temporary files before using PYSHP python library to access the shapes collection. r = shapefile.Reader(tempfile_name + '.shp') shapes = r.shapes() W...
mjb
1

votes
2

answer
132

Views

Speed up “Find points in polygon”-query in geodjango

I am working on a geodjango application where I want to find all the geogrpahic locations in a polygon. The idea is that I store all geographic information (cities, countries, POI, etc.) in a table and if I want to find everything inside a (multi)polygon that I query the database to find those point...
Yorian
1

votes
1

answer
204

Views

GeoDjango + PostGIS: pure geometry SRID

I am thinking about using django (2.0.6) with Postgres/postGIS as a backend (10/2.4?). However, the model geometries I intend to use will not be stored 'against' a spheroid (whether earth mars, etc) or any other kind of SRID. Rather a 100% pure geometry in 3D cartesian coordinates, where 1 unit = 1...
Hal
1

votes
1

answer
171

Views

Table with coordinates of a hexagonal grid that covers the world

Looking for an implementation in PostGIS for generating a hexagonal grid that covers the the whole planet in order to aggregate data over each hexagon. Any pointer in the right direction would be of great help! End product: - A table containing the center point for each hexagon in a hexagonal grid t...
user9936632
1

votes
1

answer
667

Views

postgis 2.3 installation with postgres 9.6 ubuntu 18.04

I have been trying to install postgis 2.3 with postgres 9.6 on ubuntu 18.04 with command sudo apt-get install postgresql-9.6-postgis-2.3 but got an error E: Unable to locate package postgresql-9.6-postgis-2.3 E: Couldn't find any package by glob 'postgresql-9.6-postgis-2.3' E: Couldn't find any pack...
deusxmachine
1

votes
0

answer
205

Views

OuterRef not allowed in an annotation?

I'm trying to annotate a queryset of Entity objects with the distance between two related polygons; each entity has its own set of related polygons, stored in the shape attribute of foreign-key-related EntityFeature objects. EntityFeatures are named, and I need to find the distance between two named...
bribroder
1

votes
0

answer
153

Views

How to create a DbGeometry column in a table in C# using Entity Framework Core for working with PostGis

I'm doing a C# project where I need to make some tables to use PostGis for calculate the distances of the different objects. I created the object using EntityFramework. In order to calculate the distances, I added to the object a latitude, a longitude and a Geometry object that Postgis uses. The Pos...
1

votes
1

answer
39

Views

pgsql2shp “Error executing user query”

I have been getting Initialization error while attempting the pgsql2shp postgresql code: pgsql2shp -f output.shp -h localhost -u postgres -P admin parcel 'SELECT * FROM 'public'.'Parcel' WHERE ParcelNoEng=116' Error : Initializing... Error executing user query: ERROR: syntax error at or near ''Parc...
Sharma Aashish
1

votes
0

answer
45

Views

Add constraint on geometry column in postgres table, the_geom in a country

I am creating and manipulating a lot of table with geometry, and sometimes some geometries aren't were there are suppose to be. So, I would like to had a constraint, when I create my tables, to be sure they are in the right polygon (France in my case). I taught to use CHECK (the_geom IS NOT NULL AN...
gaspareu
1

votes
1

answer
100

Views

Which datatype should be used to store GeoJSON response to Postgres

I have a table called city, which currently only contains the following columns: id name lat (for center point) long (for center point) date_created I would like to store a GeoJSON response like this one here My question is, which datatype should i use to store it ? Things to be considered Later on...
Jeremy
1

votes
0

answer
169

Views

Postgresql error when creating extension :Library not loaded: /usr/local/opt/libtiff/lib/libtiff.5.dylib

I am trying to install PostGIS and spacial libraries for Postgres as per GeoDjango docs . after installation I tried creating PostGIS extension for my database but I get the following error psql postgistest -c 'create extension postgis;' ERROR: could not load library '/usr/local/lib/postgresql/rtpo...
Ashok saravanan
1

votes
1

answer
123

Views

Efficient way to match postgis linestrings against a GPS log

I am working on a website that allows users to upload a GPX file and the website has a table full of linestrings stored in postgis. These linestrings are sections of road and trails. Then the website works out from the gpx file all the linestrings that that the gps log traveled along (the direction...
Qwertie
1

votes
1

answer
44

Views

Distance calculation in a trigger postgresql

I've got two tables 'modulo1_cella' and 'modulo2_campionamento'. The first, 'modulo1_cella' contains polygons, while the latter, 'modulo2_campionamento', contains points (samples). Now, I need to assign to each polygon the nearest sample, and the identificative of the sampler itself. Table 'public...
user2295796

View additional questions