PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

learn more… | top users | synonyms

0
votes
1answer
80 views

How can I find the segment of a line that lies within a polygon using Postgis?

Given a "road", represented by a line, that crosses a "state", represented by a polygon: What is the SQL query I can use to find the segment of the road that is within the state? A concrete example. ...
0
votes
0answers
53 views

PostGIS Function both work fine

I create two function both work fine same solve, but one of them, take to much time (which one use "temporaly"). Is very important because I will run in several times the funcion. Please any one can ...
1
vote
2answers
61 views

How to get a set of all buildings of an OpenStreetMap section in form of single points / Multiple polygons as one point

The objective is to get a set of all buildings of an Open Street Map section in form of single points in a shapfile. I downloaded the section from osm. Imported the section into QGIS (and later ...
1
vote
0answers
84 views

Open PostGIS layer with QGIS 2.2

I just upgraded QGIS from 1.8 to 2.2 and I am getting some troubles to open a PostGIS Layer. The connection with the server works fine, I can see all my tables, select the one I want to open and even ...
1
vote
1answer
47 views

Why is “Add feature” of the GPS Information tool inactive for some of my PostGIS layers?

Sorry If I rise it again on this forum but I couldn't find any info on limitations/requirements of "GPS Information" tool for QGIS (2.2.0) against data layers. For some layers I'm able to collect ...
2
votes
1answer
101 views

Reverse Geocode with PostGIS use Open Street Map Database

I want to reverse geocode with postGIS use OSM database, I use FUNCTION: <!-- language: lang-sql --> CREATE OR REPLACE FUNCTION khoangcach(lat double precision, lon double precision) RETURNS ...
0
votes
0answers
68 views

Using postgis functions in ArcGIS Server 10.2 for Layer Definition Query

I asked the previous question Using PostGIS functions in ArcGIS Desktop for Layer Definition Query? And got an excellent answer. However this covered ArcGIS Desktop. In 10.0 it seemed that if I got ...
1
vote
0answers
50 views

PostGIS raster connection is not closed by MapServer

I'm using PostGIS raster in MapServer like this: TYPE RASTER DATA "PG:port=6432 dbname='mydb' user='postgres' password='1234' schema='public' table='truemarble' mode='2'" PROCESSING ...
0
votes
2answers
68 views

How to select the cross streets in postgis spatial table?

I've used ST_Intersects and ST_Crosses but between two different tables. I have a table of streets and intersections want to know them. For example select all streets that cross the Street A. Can ...
1
vote
1answer
195 views

Setting up PostGIS: Postgres can't see the PostGIS extensions?

I've just installed Postgres 9.3.4 and PostGIS 2.1 on OSX Mavericks using the very latest versions of the KyngChaos libraries. This is a new machine, and I deliberately haven't installed Postgres ...
0
votes
2answers
53 views

Identify circuits/loops in GPS track data

This question will be naive and uneducated, but I gotta start my research somewhere, so I'll ask for references and pointers... Let's say you have a GPS time series with a set of lat/long coordinates ...
1
vote
1answer
47 views

how to add data from a layer to postgis table with different field names

I am importing waypoints from GPS (in gpx format) using Layer>Add Vector layer and then I want to clean up the data and add some more attributes and finally add them to a master table in PostGIS. The ...
0
votes
1answer
62 views

PostGIS Spatial Index not used after copying table

I am copying a PostGIS database from my computer to our webserver. I always had problems to copy the entire database using pg_dump and pg_restore so I am creating a new database on the server and copy ...
3
votes
1answer
63 views

Rounding bug using ST_GeomFromGeojson()

Let's say I have the following geographical point represented in GeoJSON: {"type":"Point","coordinates":[-109.0,35.292506]} When I run the following conversion, things work as expected: SELECT ...
0
votes
0answers
29 views

Points generated using mmqgis are invalid

I used mmqgis plugin to geocode some addresses, he pointed to have found them all. But the screen shows only a point when there are more than 100, in the attribute table all appear. I loaded this ...
0
votes
1answer
173 views

Can't load pgrouting extension, could not load library librouting_dd.ddl error 998 windows

I have a windows 7 machine 64-bit, I installed Postgresql 9.3 via the opengeo suite 4.0.2 in the program files (x86) folder, so I guess I have to install the 32-bit version of the extension. I am ...
2
votes
0answers
98 views

how do I create a RESTful PostGIS web Service Framework?

I have PostGIS tables that store vectors that want to show on Google Maps. Read the post http://gis.stackexchange.com/questions/22354/how-to-display-a-postgis-table-on-google-maps But one thing that ...
0
votes
0answers
34 views

Sort Proximity Results Into Groups Based on Distance from Center

The scenario is as follows: I have a database of pizza places with review averages. I want users to be able to find pizza places near a location and have them sorted by distance. Pretty straight ...
0
votes
0answers
47 views

How to register geometries using OpenLayers and GeoServer in PostGIS?

I put a Editing Toolbar on my page using Google Maps Api as base layer. I want to register lines and points in PostGIS. But I want to know how I can get the longitude and latitude of gemotria (line ...
1
vote
1answer
144 views

storing date range in PostgreSQL/PostGIS for time parameter in WMS/WFS

Using GeoServer I want to deliver some objects/features with WFS and WMS. The objects and their attributes have a date range during which they are valid. Say, we have street lamps and each street lamp ...
1
vote
0answers
51 views

Implement a least cost path in a graph

I am trying to produce a routing functionality in a web-GIS that, given a start and an end point, will retrieve the least cost path taking into account points along edges representing major costs, ...
0
votes
1answer
140 views

Difference between 2 projections?

I have osm.xml file where projection is defined as follows: Map background-color="grey" srs="&srs900913;" minimum-version="2.0.0"> n settings.xml file projection is also set there as: <!ENTITY ...
0
votes
1answer
78 views

PostGis geometry name lookup failed for table

I have a table name erpAssets which has column name Region of type polygon. it contains entries of points also like (-79.4609576808001,43.9726680183837)). I have written query which convert these ...
0
votes
1answer
45 views

problem transforming pgr route to a line - unexpected extra lines from St_MakeLine

I have created a line geometry from a pgr_dijkstra querry and I am getting an unexpected looking line in qgis. The following image illustrates the problem: The magenta line is the line created ...
1
vote
0answers
36 views

Import raster to postgis and add a foreign key constraint

I use raster2pgsql to import raster files into a table with this command line: C:\Program Files\PostgreSQL\9.3\bin>raster2pgsql -s 4326 -M -F -a c:\temp\mun.tif public.basqui_raster | psql -U ...
1
vote
1answer
132 views

Difference between planet_osm_polygon and planet_osm_point tables

What's the difference between the planet_osm_polygon and planet_osm_point tables? I find similar columns in both tables.
3
votes
1answer
126 views

How to render overlapping bus trips as one route from OSM?

I would like to create bus map for my city from OSM data. I imported data into Postgresql with osm2psql and I can render routes with Mapnik. But the problem is that same route ref is shown multiple ...
0
votes
0answers
35 views

Postgresql (soln would be basic SQL I think): ORDER by distance to a point and return those distances in array pairs w/ the full records

so I know there have been some questions asked about how to calculate distance using postgis and how to order results by their distance from a respective point. I'm not very talented at SQL (at all) ...
0
votes
1answer
40 views

Switch line direction for PostGIS-lines in QGIS or Pgadmin

I am trying to switch the line direction in a PostGIS-layer. The postgis-geometry column is e.g. "LINESTRING(0 0, 1 1, 2 1, 2 2)". The reason is that my symbology is direction-dependent, but I drew ...
2
votes
1answer
57 views

How can I get proj4text from srtext?

I have a custom projection used in some shapes on a prj file. I want to insert this projection in the table spatial_ref_sys PostGIS. The data in the prj file will SRTEXT table field, how can I get ...
2
votes
2answers
157 views

How to find the destination point given a start point, distance and bearing?

If I have a starting point as well as distance and a bearing to the destination point, how can I find the coordinates of the destination point using PostGIS? I'm looking for a PostGIS equivalent to ...
0
votes
1answer
31 views

PGrouting encapsulated queries error [quotes problem]

I'm facing a quote problem on a pgrouting query. What I'm thinking is wrong is that my query only works if I use one, then four, then eight quotes on encapsulated queries. Is there a way to simplify ...
0
votes
2answers
163 views

creating isochrones in postgis/osm2po/pgrouting and then saving the isochrones as polygons

So I am creating some isochrones in pgrouting based upon a table from osm2po. Now I have a query that will get all of the edges that are c driving distance away from a point. My question is, how can I ...
1
vote
2answers
101 views

adding geometries to a source, target table for multiple rows in pgrouting

I am having an issue trying to perform the pgr_dijkstra algorithm and getting back a line in my table for multiple rows. I have a table that has a columns of source, target node id's but I cannot ...
1
vote
1answer
140 views

Help defining custom CRS in QGIS 2.2

I'm trying to use EPSG:4479 in QGIS. 1) If I search for EPSG:4479, it returns a "Imported from GDAL" EPSG:4479, but it does not work. At the console, the following message is displayed: ERROR 6: ...
1
vote
1answer
50 views

PostGis line geometry type

I am using postgresql9.3 and postgis2.1.I imported a shape file into my database. In that the_geom datatype is "LINESTRING" . Now i need a query to find the lines within a given radius for a ...
1
vote
1answer
296 views

Is it possible to get a binary geometry from PostGIS?

PostgreSQL (via C# Npgsql driver) returns geometry column (PostGIS) like a hex string (EWKB as I understand) for this query: SELECT ST_Transform(geometry, 4326) FROM myGeometries; Result (for ...
2
votes
0answers
66 views

Feature service requires registered database error?

I am working on ArcGIS Server 10.2 and SQL Server 2008. when I want to publish the service, I get this error "Feature service requires a registered database" that's error happen when I read (List By ...
1
vote
0answers
77 views

Gaussian plume model in PostGIS or geocouch

Does anyone know of existing code for dispersion modeling using Gaussian Plume or comparable model, that I could plug in / adapt to be used programmatically from PostGIS or alternatively via ...
1
vote
1answer
60 views

TIGER Geocoder not matching intersections without ZIP

I'm using the TIGER geocoder (2013 data, PostGIS 2.1, Postgres 9.3) to geocode intersections in New York City, but I'm finding that if I omit the zip code, I almost never get a match. So, for ...
2
votes
1answer
27 views

Modeling data in PostGIS when Several Join queries with Geometry are Needed

Suppose you have a database where you have several tables with geometric information and you have several cases in your system where you need to query the tables with a lot of joins (ie: LEFT JOIN ...
0
votes
1answer
58 views

Get latitude and longitude of each point forming a MultiLineString

A MultiLineString is formed by a sequence of points and each point has latitude and longitude. Use the sql below to get the latitudes and longitudes of each point so that my MultiLineString: SELECT ...
1
vote
1answer
59 views

Can't get Tiger Geocoder to work in PostGIS

I've installed the PostGIS Tiger Geocoder according to these instructions and loaded in data for the state of New York. Unfortunately, when I try to geocode something... ny_tiger=# SELECT g.rating, ...
0
votes
1answer
73 views

convert data from osm to osm tiles/marble

I need to convert the raw thata OSM XML to tiles styled like standard OSM tiles (and other styles if possible) From the research I've done I've found I need to have osm2pgsql and put it on the same ...
3
votes
1answer
349 views

Problem PostGIS Spatial Query ST_Contains ST_Disjoint ST_Intersects

I'm working on a project with two different tables inside a database. One contains points in a column geometry(Point,4326) type and the second one has a column geometry(MultiPolygon,4326). Now I would ...
0
votes
1answer
34 views

Smartest way to union different “layers” in postgis

I'm currently using this crude approach to combine several tables/layers : CREATE TEMPORARY VIEW p_impd_800 AS ( SELECT st_union(geom) as geom FROM rt_201 UNION SELECT st_union(geom) as geom FROM ...
2
votes
0answers
153 views

How to fix “Raster layer: Provider is not valid” error when loading PostGIS rasters?

I compiled and installed the current QGIS master as usual and I imported some raster data into my postgres 9.3 database with postgis 2.0.2 extension. Whenever I try to import the raster data into qgis ...
-1
votes
2answers
450 views

Convert geometry to latitude-longitude using PostGIS ST_Transform

My table has a geometric field, in EPSG:3857, of type point. I need the latitude and longitude, in EPSG: 4326, of each point. Could someone give me a valid example of how to use ST_Transform().
1
vote
0answers
123 views

Why does QGIS crash when my PostGIS table has a primary key?

Configuration: PostGIS Library: 2.0.3 GEOS: 3.3.8-CAPI-1.7.8 Proj: Rel. 4.8.0, 6 March 2012 PostgreSQL 9.2.2, compiled by VC++ build 1600, 64 bit, running on Win2012 server 64 bit QGIS 2.2.0 - 32 ...
1
vote
2answers
126 views

Most optimal way to create a (spatial) database for big amounts of data?

I am using QGIS, and on my way to set up a huge spatial database (geographical), where most of the data is points without geometry), along with other types of data (like lines in DXF and areas in ...