Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
8,984
questions
124
votes
7
answers
57k
views
Spatial clustering with PostGIS?
I'm looking for spatial clustering algorithm for using it within PostGIS-enabled database for point features. I'm going to write plpgsql function that takes distance between points within the same ...
- 6,828
105
votes
4
answers
37k
views
What are the pros and cons of PostGIS geography and geometry types?
My company uses geometry (the_geom) data type for storing geospatial data.
I've recently been acquainted to the concept of geography (the_geog) data type which, as I understand it, stores the SRID ...
- 6,700
92
votes
4
answers
177k
views
Getting coordinates from geometry in PostGIS
This line:
SELECT geom
FROM myLayer.geom
will return a hex value.
How would I get decimal values, which is a human-readable coordination?
- 1,219
85
votes
5
answers
15k
views
Getting started with PostGIS?
I have been reading a lot about PostGIS, but I am struggling to find a good beginner's guide.
Which tutorials/books do you recommend?
75
votes
7
answers
31k
views
Creating regular polygon grid in PostGIS
How to create, on the shape of a polygon, a regular grid of polygons/squares of an given size, in PostGIS?
I have thought about a function like Creating regular point grid inside polygon in PostGIS ...
- 973
70
votes
2
answers
13k
views
Acquiring ArcGIS-like speed in Postgis
I have been using Postgis 2.0 for 3/4 of a year now and while I really enjoy using it, excessive query processing time has rendered it basically unusable for my use case.
I tend to do heavy ...
- 1,611
68
votes
1
answer
59k
views
Choosing SRID and what is its meaning? [closed]
I'm new to GIS, in PostGIS, and I'm struggling with the SRID concept.
How do you choose a SRID for a database column? This is making me crazy. Why do I need to choose a SRID? What's its meaning?
...
- 861
60
votes
3
answers
53k
views
Changing SRID of existing data in PostGIS?
While importing my shapefile data to PostGIS, I did not select the proper Projection.
How do I now change the SRID of the data, without transforming the Coordinates?
- 40.5k
60
votes
7
answers
13k
views
Creating Vector Polygons with rendering performance like GISCloud?
I have been looking for a solid solution which would allow me to create a web map and overlay vector polygons without taking forever to load such data with the goal of allowing me to make each polygon ...
- 2,316
60
votes
2
answers
128k
views
Inserting point into PostGIS?
I have created one table in my PostGIS nut I cannot insert point.
What is wrong with my query?
CREATE TABLE app (
p_id INTEGER PRIMARY KEY
);
SELECT AddGeometryColumn('app','the_geom','4326','...
- 1,153
57
votes
3
answers
13k
views
What is the purpose of PostGIS on PostgreSQL?
PostgreSQL already supports spatial data types, operators and indexing.
What does PostGIS provide exactly that made it necessary to exist as an extension to PostgreSQL?
Why don't we all just use ...
- 749
55
votes
3
answers
50k
views
How can I get a shapefile from a postgis query?
My data is in a PostGIS database. I would like to generate a shapefile from a query. How can I do that?
- 5,194
54
votes
4
answers
41k
views
Making spatial view in PostGIS and adding it as layer in QGIS?
In PostGIS 2.0, I am seeking an efficient way to create a spatially enabled view that is viewable in QGIS.
I followed the context of Part 2 of @Mike Toews answer to How to create a table from ...
- 22.8k
53
votes
5
answers
36k
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,493
53
votes
10
answers
25k
views
Selecting features that do NOT intersect in PostGIS
Using PostGIS, I just want to select points that fall outside of polygons. Ultimately this is the inverse of the ST_Intersects, as far as I can see it.
Example: I have a taxlot layer and a address ...
- 22.8k
53
votes
3
answers
16k
views
Meaning of Simplify's tolerance parameter
The standard parameters of Douglas-Peucker's simplify algorithm are geometry and tolerance (e.g ST_Simplify in PostGIS). What's the meaning of the tolerance parameter? I know that the bigger the value,...
- 867
50
votes
4
answers
47k
views
Joining lots of small polygons to form larger polygon using PostGIS?
I have the following layer using SRID 27700 in postgis:
It's every administrative region in the UK, and (as you can see from the colour grouping) each of them has a text field specifying the county ...
- 1,711
49
votes
2
answers
69k
views
How do I check which version of GDAL I have and how many?
I have installed in my computer the following software:
PosgresSQL 9.3.2.3
PostGIS 2.1.1.1
QGIS 2.0.1 Dufour (this one installed through the OSGeo4w package)
QGIS 1.7.0 Wroclaw
As far as I know all ...
- 1,485
47
votes
3
answers
21k
views
Reprojecting all geometries in PostGIS table?
I created a spatial table with SRID:4326. Now I want to change total projection to SRID:32644 into a new table. The old table should remain unchanged.
- 1,359
47
votes
7
answers
12k
views
Tuning PostGIS for production environments?
What PostGIS documentation have you come across that has been helpful when tuning PostGIS for a production environment?
I would like to get together with my DBA to setup a Postgresql/PostGIS ...
45
votes
3
answers
56k
views
Adding shapefiles to PostGIS database
I was able to create a Postgresql database and then I executed,
createlang plpgsql geospecies
psql -d geospecies -f postgis.sql
psql -d geospecies -f spatial_ref_sys.sql
So now can I directly import ...
- 4,335
44
votes
5
answers
26k
views
Calculating average width of polygon? [closed]
I'm interested in examining the average width of a polygon that represents the road surface. I also have the road centerline as a vector (which is sometimes not exactly in the center). In this example,...
- 16.1k
43
votes
4
answers
49k
views
How to insert a GeoJSON polygon into a PostGIS table?
I need to insert a polygon from GeoJSON to my PostGIS table. This is how the SQL query looks like.
INSERT INTO tablename (name, polygon)
VALUES (
'Name',
ST_GeomFromGeoJSON(
'{
...
- 1,134
43
votes
5
answers
14k
views
Curved point-to-point "route maps" [closed]
I have recently been looking at airlines web pages that display their routes departing a certain city to all other cities they service. I would like to be able to create similar curved routes between ...
- 22.8k
42
votes
3
answers
60k
views
Creating geometry from lat/lon in table using PostGIS?
I have a table with over 800,000 records including a lat and long column. The data source did not create geometries, so I was looking around how to do this. I know there's ST_MakePoint(x,y,SRID) but ...
- 531
42
votes
5
answers
35k
views
Fixing non-noded intersection problem using PostGIS
I'm using a PL/R function and PostGIS to generate voronoi polygons around a set of points. The function that I am using is defined here. When I use this function on a particular dataset I get the ...
- 16.1k
41
votes
1
answer
39k
views
How to convert/typecast from Polygon to Multipolygon in PostGIS?
Is there any way to typecast Polygons to Multipolygon type?
For example, I have created a circle using ST_Buffer() on a point. The return type of that function is of Polygon type. The problem is that ...
- 1,794
41
votes
4
answers
37k
views
Select bounding box using postGIS?
I want to create a query to select all ways and their nodes that exist within a bounding box using postGIS. The bounding box shall includes all details as osmosis "--bounding-box" command will ...
- 587
40
votes
6
answers
24k
views
Shapefile PRJ to PostGIS SRID lookup table?
I was wondering if there is such a thing as an shapefile PRJ to PostGIS SRID lookup table? Something that can translate the most standard shapefile PRJ definitions into the likely SRID.
When using ...
- 22.8k
40
votes
6
answers
22k
views
Migrating geodatabase data into PostGIS without Esri apps?
Can you tell me how I can load geodatabase data into PostGIS without having to install Esri apps?
The solution I found online talked about using ArcGIS to convert GDB to SHP and then importing SHP ...
- 501
39
votes
12
answers
12k
views
Creating regular point grid inside polygon in PostGIS
How to create, inside a polygon, a regular grid of point spaced x,y in PostGIS?
Like the example:
- 9,759
39
votes
3
answers
46k
views
Performing bounding box query in PostGIS?
I have a PostgreSQL table, with almost 2 million rows, with a long-lat coordinates field in the form POINT(-73.4938 33.2405).
Supposing there's a geospatial index on that field, what's the most ...
- 923
39
votes
4
answers
88k
views
Return all results within a 30km radius of a specific lat/long point?
I have a table with a column the_geom which contains data similar to:
...
- 575
38
votes
21
answers
34k
views
Connecting to PostGIS database from ArcMap for display and query without ArcSDE?
How can I connect to a PostGIS database from ArcMap using ArcGIS Desktop 9.3 and later?
I would like to be able to perform spatially enabled queries and receive the results back (e.g. spatial and non-...
- 9,361
38
votes
4
answers
13k
views
Separate polygons based on intersection using PostGIS
I have a PostGIS table of polygons where some intersect with one another. This is what I'm trying to do:
For a given polygon selected by id, give me all of the polygons that intersect. Basically, ...
- 1,732
37
votes
5
answers
38k
views
Why does PostGIS installation not create a template_postgis?
I went this route for installation of PostgreSQL and PostGIS:
PostgreSQL using the standalone KyngChaos,
PostGIS install from KyngChaos as well...
pgAdmin3 from PostgreSQL Tools
Now when I launch ...
- 5,383
37
votes
7
answers
25k
views
Nearest neighbor between point layer and line layer? [closed]
I've asked this question several times on stackoverflow and irc between #qgis and #postgis and I also tried to code it or implement it my self in postgis with no real answer.
Using programming (most ...
- 8,523
37
votes
2
answers
3k
views
Choosing coordinate system to store geography data for celestial coordinates
I am doing an astronomy project. I want to have the information about our images stored in a spatially enabled database. This, I would think, should be a very easy special case for GIS functions ...
- 473
36
votes
2
answers
34k
views
Changing geometry type from Point to Multipoint within existing table in PostGIS?
Is there a PostGIS function that can change geometry type for an existing table?
We need to change from POINT to MULTIPOINT.
The table will be empty when we change the geometry type and we cannot ...
- 361
36
votes
3
answers
40k
views
Best GIS system for high performance web application - PostGIS vs MongoDB
I am working on a web/mobile application based on location data. Since i am already familiar with MongoDB, i found the geospatial indexing of mongo is quite suitable for my needs. As i am mainly ...
- 515
36
votes
2
answers
17k
views
Point in Polygon analysis of 200 million points [closed]
I have a CSV containing 200 million observations with the following format:
id,x1,y1,x2,y2,day,color
1,"-105.4652334","39.2586939","-105.4321296","39.2236632",&...
- 371
35
votes
2
answers
30k
views
What is the unit used in ST_Distance()?
I wonder what the unit is of the float being returned from ST_Distance.
In the documentation it says:
...cartesian minimum distance (based on spatial ref) between two geometries in projected units....
- 2,239
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
34
votes
5
answers
43k
views
PostGIS nearest points with ST_Distance, kNN
I need to obtain on each element on one table the closest point of another table. The first table contains traffic signs and the second one the Entrance Halls of the town.
The thing is that I can't ...
- 3,608
34
votes
4
answers
12k
views
Bulk load multiple shapefiles into PostGIS
I've got over a hundred data layers in shapefile format that I want to upload into a PostGIS database. They are all in the same projection, but represent different data layers so they do not have the ...
- 22.8k
33
votes
6
answers
43k
views
PostGIS Geometry type not matching column type in QGIS
I'm trying to import some polygon shapefiles to Postgis in QGIS via SPIT. One of them can't be imported and returns this error:
ERROR: Geometry type (Polygon) does not match column type (MultiPolygon)...
- 6,512
33
votes
3
answers
15k
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,637
32
votes
4
answers
7k
views
When should you NOT use a spatial index?
I am asking this because I was mainly working with Oracle but for the past year I've been doubling with PostGIS and SQLServer 2008. Most spatial functions in Oracle won't work without a spatial index ...
- 2,340
32
votes
2
answers
19k
views
What is the Difference between Geometric and Geographic columns? [duplicate]
I am a beginner in GIS and Learning PostGIS. I came across this two terms Geom and Geog, Could anyone explain in brief. What is the best to use with map data and Do all functions support both Geom and ...
- 819
32
votes
2
answers
8k
views
How is ST_PointOnSurface calculated?
The PostGIS documentation states that ST_PointOnSurface returns "a POINT guaranteed to lie on the surface". It seems like this function could be trivially implemented to give results that satisfy the ...
- 423