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
117 votes
7 answers
55k 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 ...
user avatar
  • 6,648
102 votes
4 answers
35k 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 ...
user avatar
  • 6,564
87 votes
4 answers
167k 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?
user avatar
  • 1,169
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?
73 votes
7 answers
29k 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 ...
user avatar
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 ...
user avatar
  • 1,611
67 votes
1 answer
56k 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? ...
user avatar
  • 851
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 ...
user avatar
59 votes
2 answers
122k 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','...
user avatar
  • 1,143
58 votes
3 answers
50k 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?
user avatar
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 ...
user avatar
  • 749
54 votes
4 answers
39k 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 ...
user avatar
  • 22.4k
52 votes
3 answers
48k 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?
user avatar
51 votes
4 answers
34k 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 ( ...
user avatar
51 votes
9 answers
23k 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 ...
user avatar
  • 22.4k
51 votes
3 answers
15k 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,...
user avatar
  • 847
49 votes
4 answers
45k 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 ...
user avatar
  • 1,683
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 ...
46 votes
3 answers
20k 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.
user avatar
44 votes
5 answers
24k 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,...
user avatar
  • 15.9k
44 votes
3 answers
54k 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 ...
user avatar
  • 4,275
44 votes
2 answers
63k 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 ...
user avatar
43 votes
5 answers
13k views

Curved point-to-point "route maps"

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 ...
user avatar
  • 22.4k
42 votes
5 answers
33k 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 ...
user avatar
  • 15.9k
41 votes
4 answers
46k 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( '{ ...
user avatar
  • 1,104
41 votes
1 answer
36k 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 ...
user avatar
  • 1,766
40 votes
6 answers
23k 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 ...
user avatar
  • 22.4k
40 votes
4 answers
36k 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 ...
user avatar
  • 577
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 ...
user avatar
39 votes
11 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:
user avatar
  • 9,753
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-...
user avatar
  • 9,311
37 votes
3 answers
51k 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 ...
user avatar
  • 481
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 ...
user avatar
37 votes
7 answers
24k 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 ...
user avatar
  • 8,442
37 votes
4 answers
12k 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, ...
user avatar
  • 1,674
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 ...
user avatar
  • 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 ...
user avatar
36 votes
3 answers
43k 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 ...
user avatar
  • 893
36 votes
3 answers
39k 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 ...
user avatar
  • 515
36 votes
4 answers
82k 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: ...
user avatar
  • 545
35 votes
2 answers
16k views

Looking for fastest solution for 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","Monday","Black" 2,"-105.3224523","39....
user avatar
  • 361
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 ...
user avatar
  • 451
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 ...
user avatar
  • 22.4k
33 votes
5 answers
40k 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 ...
user avatar
  • 3,500
33 votes
2 answers
29k 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....
user avatar
  • 2,129
32 votes
6 answers
40k 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)...
user avatar
  • 6,404
32 votes
4 answers
6k 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 ...
user avatar
  • 2,334
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 ...
user avatar
  • 809
32 votes
3 answers
14k 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 ...
user avatar
  • 1,567
31 votes
1 answer
7k 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 ...
user avatar
  • 413

1
2 3 4 5
174