Questions tagged [postgresql]
PostgreSQL is an open source object-relational database system.
516
questions
26
votes
1
answer
16k
views
Setting up indexes for PostGIS distance queries
I'm building an application that is supposed to query and return every Record in a table that is X kilometers away from PointX. Records and PointX's positions are determined from (long/lat) ...
- 363
12
votes
2
answers
5k
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:
...
- 3,407
34
votes
3
answers
17k
views
Which function for creating a POINT in PostGIS?
When defining a Point in PostGIS, when do you decide to use which of the following?
ST_SetSRID(ST_MakePoint(lon,lat),4326)
ST_SetSRID(ST_Point(long,lat),4326)
ST_SetSRID(ST_GeomFromText('POINT(lon ...
- 1,687
21
votes
10
answers
34k
views
Broken packages and unmet dependency installation QGIS, PostgreSQL and postgis Ubuntu 14.04
I am aware that this could be a duplicate topic but I am struggling for a several days.
I upgraded QGIS from 2.8 to 2.18 and after that everything get broken. I couldn't use PostGIS extension on ...
- 573
59
votes
6
answers
41k
views
SQL query to have a complete geojson feature from PostGIS?
I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature.
SELECT row_to_json(fc)
FROM ( ...
- 3,603
35
votes
0
answers
18k
views
How to use PostgreSQL/PostGIS with ArcGIS for Desktop? [duplicate]
Has anyone used PostgreSQL with PostGIS successfully?
I am a semi-experienced ArcGIS user and want to build out a database and connect it to ArcCatalog.
I've scoured the internet for resources but ...
- 451
13
votes
1
answer
1k
views
Removing overlaps and keeping highest priority polygon using PostGIS
I have a multipolygon table with overlapping and non overlapping multipolygons. Where they overlap I need to keep the one with the highest (=1) priority and erase away from the lower priority (prio ...
- 74.6k
8
votes
2
answers
3k
views
Finding nearest neighbor for each point in the same table
We've got a table of approximately 300,000 points and would like to find the nearest neighbor (within 10km) to each point in the same table. The most efficient query we've found so far is below, but ...
- 657
12
votes
1
answer
5k
views
Is the Geometry(4326) an alias for Geography datatype?
The main reference about Geography datatype seems docs/using_postgis_dbmanagement:
Prior to PostGIS 2.2, the geography type only supported WGS 84 long lat (SRID:4326). For PostGIS 2.2 and above, any ...
- 2,304
4
votes
1
answer
2k
views
Efficient way to find nearest feature between huge postgres tables
I have two tables (g1 and g2), both with around two million features. I need to know for each feature in g1, which is the closest feature from g2, with a condition. Since they are both so huge, I also ...
- 292
2
votes
1
answer
534
views
PostGIS: ST_ClosestPoint join error
I have two tables: one with geom points of species and an other one with height contour lines geoms. My Idea was to find per specie geom the closest point to a contour 'geom' line and then select its ...
- 123
6
votes
3
answers
2k
views
Numbering polygons according to their spatial relationships?
I have, for example, this layer in postgresql (polygons):
table : poly (objectid: integer, num: integer, shape: geometry)
I want to enumerate the polygons from left to right and top to bottom like ...
- 645
1
vote
1
answer
226
views
Get barrier edge id
I have a table of POINTs that represent barriers. I would like to be able to find the closest edge id on the edge graph so that I can have the routing algorithm ignore that edge (thus route around it)....
- 125
21
votes
1
answer
29k
views
Import a shapefile to postgis with ogr2ogr gives: Unable to open datasource
I want to use ogr2ogr in order to import a shapefile in a postgis database.
I have successfully installed ogr2ogr and I run from the pgsql the following command:
ogr2ogr -f "PostgreSQL" PG:"host=...
- 2,654
13
votes
5
answers
19k
views
Percentage of polygon in one shapefile within polygon of another
I have two shapefiles: 1. an administrative boundary layer for a county in the UK known as an LSOA boundary that has 500 little zones in it 2. a flood zone.
Ideally, I want to find out which of the ...
- 823