Questions tagged [postgresql]
PostgreSQL is an open source object-relational database system.
517
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.8k
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
3k
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
536
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
228
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
5
votes
3
answers
13k
views
How to enable SFCGAL in PostGIS?
I have the problem with the enabling extension SFCGAL.
CREATE EXTENSION postgis_sfcgal;
doesn't work for me even I have the PostGIS v2.2.
When I do that, it throws an error
ERROR: could not ...
- 131
3
votes
1
answer
1k
views
Find closest polygon from point and get its attributes?
Given a set of points and a set of polygons, I am trying to find for each point what the closest polygon is and bring over the attributes from that polygon to the point (together with the actual ...
- 1,236
30
votes
2
answers
22k
views
In PostGIS is it possible to create a View with a unique ID?
When I create a view in PostGIS is there any way to add a unique ID to that view? Just like the "gid" field in any other PostGIS table?
Edit: Sorry I should have included this in the original post. I ...
- 3,039
27
votes
6
answers
35k
views
Shapefile loader in pgAdmin 4
Recently I have updated my postgresql version. I have also upgraded pgAdmin from 3 to 4. But I was unable to load shapefile. I didn't find any plugin called shapefile loader. How can I get the ...
- 3,189
26
votes
3
answers
8k
views
Publishing PostGIS Rasters in GeoServer?
How do I publish PostGIS rasters using GeoServer?
I have spent a lot of time trying to create a Raster datasource using Image Mosaic JDBC but with no luck.
Steps performed:
1. Downloaded and ...
- 809
21
votes
3
answers
10k
views
Identifying road intersections using PostGIS
I'm trying to identify where roads intersect each other, and to make a point at this intersection, with the number of roads that form the intersection listed.
I was wondering if there was some way ...
- 16.3k
19
votes
3
answers
8k
views
PostgreSQL trouble editing points / lines / polygons in QGIS
I have a problem with PostgreSQL and QGIS:
I can add points/lines/polygons and fillup field
But I can't :
Delete points/lines/polygons
Modify points/lines/polygons (move or modify field)
ex : ...
- 629
17
votes
6
answers
12k
views
Deleting duplicate geometry in PostGIS tables
After - I don't know what happened - all my entries in my PostGIS tables are doubled. I tried this to delete them but it does not delete any/all duplicates:
DELETE FROM planet_osm_point
WHERE ...
- 8,338
13
votes
6
answers
10k
views
PostGIS doesn't use spatial index with ST_Intersects
I imported the data from openstreetmap using osm2pgsql and copied it another table structure. If I made a query for containing rows using st_intersects, st_contain or someone else, postgis don't use ...
12
votes
1
answer
2k
views
Batch load multiple shapefiles to Postgis
According to the shp2pgsql help, the options drop, append, create and prepare are mutually exclusive. So, if I want to create a table from a shape and then append multiple other shapefiles, I do ...
- 13.7k
10
votes
2
answers
5k
views
Creating custom Coordinate System in PostGIS
I have a Wayne county Michigan parcel dataset with a custom SRID:
when I bring it into ArcGIS
Projected Coordinate System: NAD_1983_HARN_StatePlane_Michigan_South_FIPS_2113_Feet_Intl
Projection: ...
- 4,536
9
votes
1
answer
5k
views
Using PostgreSQL with QGIS and ArcGIS?
Is it possible to use PostgreSQL with QGIS and ArcGIS? i.e. one database for different clients.
Is it anything that I have worry or any problems to use the DBMS from two different software?
I know ...
- 3,444
7
votes
1
answer
2k
views
Automatically Snapping Points to Closest Part of Line
I have some line points representing manholes. I also have line features representing roads. Both of these features are currently living in a PostGIS database. I'd like to automatically move these ...
- 71
7
votes
2
answers
19k
views
What does 'DBMS table not found' message mean?
I have a plain postgressql table (version 8.2). I am trying to connect to it to it from ArcGIS desktop 10.0, and use as a query layer, as given in this document: http://help.arcgis.com/en/...
- 41.4k
6
votes
2
answers
19k
views
How to find nearest line from point using PostGIS?
I have a table of Roads in PostGIS which geometry is "geometry(MultiLineString,4326)".
Its attribute are
gid osm_id name ref type oneway bridge maxspeed geom
Now i want to know the ...
- 251