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

learn more… | top users | synonyms

2
votes
2answers
2k views

What is gin_trgm_ops of postGIS

I am using a query where the following line written CREATE INDEX trgm_idx ON public.roads_r USING gin (name gin_trgm_ops); Its returning the following error operator class "gin_trgm_ops" does ...
0
votes
2answers
662 views

PostGIS ERROR: function x(geometry) does not exist

I am using the following postgis query for updating a pgrouting table from my custom road data. In the x(ST_StartPoint(part_geom)) as x1, line its returning an error mentioned ERROR: function ...
4
votes
1answer
532 views

PostGIS spatial reference ID (SRID) for regular cartesian coordinate system?

I would like to store a polygon as a set of 2D points represented as x, y coordinates of a Cartesian coordinate system. Which spatial reference can I use? Looking at my spatial_ref_sys table, all the ...
0
votes
1answer
369 views

Why postgis st_overlaps reports Qgis' “avoid intersections” generated polygon as overlapping with others?

I am implementing a postgis trigger on polygon layer to check and reject newly created polygon if it overlaps others. The problem shows when I digitize new polygons adjacent to others in Quantum GIS ...
0
votes
1answer
241 views

Circles created by points inside bounding box query - PostGIS

The table that i want to perform this query has 3 columns: id, the_geom and seen_count. The query i've been using to get points inside a bounding box is as follows: SELECT id, ST_AsGeoJSON(the_geom) ...
3
votes
1answer
728 views

A GeoServer layer is based on PostGis view, but featureID is random (PK is ignored)

I have a layer in GeoServer. It is based on PostGis view. It seems that GeoServer generates random featureIDs. The view has the same columns as the underlying table, including the PK column. But ...
1
vote
1answer
5k views

Import Error in Postgis: AddGeometryColumn does not exist [closed]

Importing with configuration: ind_adm2, public, geom, C:\Program Files\OpenGeo\OpenGeo Suite\webapps\geoserver\india map\IND_adm2, mode=c, dump=1, simple=0, geography=0, index=1, shape=1, srid=0 ...
1
vote
2answers
993 views

ST_DWithin uses Geometry or Geography type

assuming I use the following query to match the locations to a poi within 10km distance: SELECT poi.poi_id, h.loc_id, (ST_Distance(poi.geom,h.geom)*111.111) as distanceinkm FROM iut_poi_transl AS ...
0
votes
1answer
86 views

Anyone recognize this (reference-system related?) pattern of distortion?

I'm new to GIS and was making some progress until getting rather stumped on this one. I've imported what I believe to be lat/lon data based on US FIPS county codes in weather alerts (NOAA SPC). When ...
2
votes
2answers
370 views

3D KNN index in PostGIS

I am looking for a way to do fast KNN queries on a dataset of 10+ million 3D points (and growing). I was hoping PostGIS would do the trick with its new <-> and <#> operators, but they don't seem ...
2
votes
1answer
353 views

Are there Open Source alternatives to ArcGIS Aggregate and Extract Value to Points?

I am looking for some kind of Open source GIS library which can perform spatial analytic functions same as ArcGIS. My use case is : I am processing GeoTIFF file provided by USGS and performing ...
3
votes
3answers
745 views

Why is sum of st_length() of segments 20% too big?

I'm running a query to return the total length of some bike paths, from OpenStreetMap. Compared to the known actual length of these paths, they're all a bit too big - something like 10-30%. I know it ...
3
votes
1answer
848 views

Auto-increment id when adding a feature to a PostGIS layer in QGIS

when I enter edit mode of a PostGIS layer in QGIS 1.8 and add a feature, is it possible to auto-increment the ID? I always have to look what last used ID was and manually enter a new one. I can't ...
2
votes
1answer
562 views

Find nearest neighbours faster in PostGIS

I currently have a query that calculates the number of useful amenities within a certain radius of each town, using OSM data: create table places as select town.name, place, town.way, ( select ...
1
vote
0answers
3k views

How to write a point-in-polygon query for Postgis?

I'm trying to write a query in Postgis which will return all rows where a dynamically changing longitude/latitude point resides within the geometric polygon of that row (in the geom column). This is ...
0
votes
2answers
110 views

SQL query for raster point samping within postgis

I am trying to update my table with the values from a raster stored within the database. Edit: I've posted the answer bellow: http://gis.stackexchange.com/a/63655/6711 The query I'm trying to use is ...
1
vote
1answer
151 views

Postgis max contains query

I have two tables: CREATE TABLE periphereies ( gid serial NOT NULL, per character varying(60), geom geometry(MultiPolygon), CONSTRAINT periphereies_pkey PRIMARY KEY (gid) ) WITH ( ...
2
votes
2answers
440 views

Problem with PostGIS in Opengeo Suite in Ubuntu 13?

Everything is working fine after installing the Opengeo Suite in Ubuntu except PostGIS issues. In the dashboard the PostGIS manage is disabled(grey) but PostgreSQL is working via pgadmin3. And also ...
0
votes
2answers
691 views

Mapserver mapfile with multiple layers that pull from postgis only gives broken image

I'm trying to use this mapfile that I made: MAP IMAGETYPE PNG EXTENT -90.000124 41.000032 -88.000044 42.000035 SIZE 1600 800 IMAGECOLOR 255 0 1 OUTPUTFORMAT NAME png DRIVER AGG/PNG ...
1
vote
2answers
67 views

Insert into X coordinate separately?

I have a postgis table with a point geometry for lat/long but i want to run a query that inserts a new row with lat, long values separately instead of concatenating them into a geometry. so is there ...
1
vote
0answers
68 views

Query OSM ids by name in PostGIS

I installed PostGIS on a server and now I want to query the OSM ids referring to a name, in order to get a list of all geometries according to this name. Is there an efficient way to query this ids ...
2
votes
2answers
542 views

How to load layers from postgis to dotspatial or sharemap viewer

I want to develop a PostGIS viewer for PostgreSQL (with SQL query editor) to visualize PostGIS layers. Which spatial library (.net C# supported) do I have to use?
0
votes
2answers
481 views

PHP PostGIS to geoJSON missing required parametar error

I'm using this PHP PostGIS to geoJSON script: https://gist.github.com/bmcbride/1913855 But I keep getting this error: 'missing required parameter: geotable' This is my code: # Retrive URL ...
1
vote
4answers
293 views

Web service to see postgis polygons

Is there any website or web service where I throw a polygon postgis and he show me on the map this polygon. Example: I put this POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)) and the service show me the ...
1
vote
0answers
61 views

windows function with points in a mixed (multipoint/point) table

I have a table comprising multipoints (tracks) and points (stops between tracks). there can be several consecutive multipoints. now, i want to merge any points lying very closely to each other ...
0
votes
1answer
107 views

I have line strings in points and need to conver them to lon/lat degrees

I need to convert line strings back to latitude & longitude coordinates. Here are is an example, please keep in mind that they do NOT reference the same point. I have included the values to ...
3
votes
1answer
205 views

Is it possible to update only latitude or longitude in postgis?

I have a postgis database that contains Points created based on a couple (longitude, latitude), and I need to update only one of those two for a given point. I found that it's possible to do so but ...
0
votes
1answer
913 views

java.lang.NullPointerException with GeoServer and Postgis

I am continuing work that another person began based on Geoserver and postgis. On the database level there are many tables and the layers are views based on these tables. That is working great! Now I ...
0
votes
1answer
343 views

geoserver problem to join sql-server table to a layer

i have a layer published on geoserver based on a connection to a postgres/postgis Table and i need to add another table existing on sql server to do a data jointure table to table in order to add ...
1
vote
1answer
487 views

Loading PGadmin III GUI Linux

Having tested out the functionality of OpenGeo Suite Community, I have installed the Linux version on my Centos 5 Server. However unlike in windows the dashboard is greyed out for Post GIS. I believe ...
2
votes
1answer
648 views

Help building SQL query to get a list of streets of a city

From two layers in PostGIS (osm_places and osm_roads), I would need to have the roads belonging to a certain city. It would be fair enough to get the street names of all streets in a radius of X ...
4
votes
2answers
3k views

Conversion from 3 dimension geometry to 2 dimension

I have a Point table in which the geometry column contains both 3 dimension and 2 dimension values.I want to convert this 3 dimensional geometry to 2 dimension.Is there any function in PostGIS? Please ...
1
vote
1answer
140 views

Algorithm to find the polygon ID where a point belongs to

I am wondering what is the simplest way to perform a look up of polygon Id based on the point location. Input: a point with coordinates, a table of polygons Output: the polygon Id where a point is ...
2
votes
0answers
591 views

Geoserver - empty native SRS field

I'm converting layers from shape files to postgis with the below command: shp2pgsql -s 22175 -c -W "latin1" path/file.dbf' table_name db_name | psql -d db_name Next, I add this layer to the ...
3
votes
2answers
706 views

In PostGIS, how do I merge a multilinestring into a single linestring, keeping the pieces in proper order?

I have a multilinestring made up of several pieces, some of which are facing the wrong direction. (Accessible here: http://pastebin.com/89Zcixv5 ) I think what I'm looking for is some utilization of ...
2
votes
1answer
219 views

Geoserver 2.3.1 not finding all columns from PostGIS connection

so I am not sure if I have missed something or this a particular problem with this version of Geoserver. I want to style a point layer with a SLD, based on values in a particular column. The column is ...
2
votes
1answer
247 views

Error printing pdf in OpenGeo Suite client SDK app

I am using OpenGeo Suite 3.0.2. I created an app using Client SDK and stored the raster and vector data on postgreSQL/PostGIS. The print tool is not working, giving error the following error: ...
0
votes
0answers
214 views

several fields and GEOMETRY GetFeatueInfo from Geoserver

Good day!. I have a table in PostGIS 2.0 with several fields of type GEOMETRY. In the geometry_columns they are published in Geoserver 2.2 as layers. This is done as here - geoserver docs, but ...
-1
votes
1answer
102 views

How to join two table based on one unique column in PostGIS [closed]

I wonder if there any simple query that perform just like Join features of ArcGIS Desktop.
0
votes
1answer
278 views

It seems there is a bug in St_Clip function of PostGIS

The code I am using for clip feature is : raster ST_Clip(raster rast, geometry geom, double precision[] nodataval=NULL, boolean crop=true); I tried clip a shape depends on the polygon from the ...
1
vote
1answer
2k views

Distance between DB field and Point using PostGIS

I'm using Postgresql 9.0 with PostGIS 2.0 extension. I've got the table and geographic field location: =# SELECT * FROM geography_columns; f_table_catalog | f_table_schema | f_table_name | ...
0
votes
1answer
266 views

st_linemerge does not work

I have two LINESTRING's which intersect by checking with st_intersects. Then i try to collect/union both linestrings into one Multistring, then try to merge with st_linemerge, but it fails with: ...
0
votes
2answers
1k views

ST_Touches for more than just one geometry

I'd like to select some neighbor polygons with ST_Touches. Therefore I found the following statement: SELECT b.name FROM layera as a JOIN layera as b ON ST_Touches((SELECT a.geom FROM layera as a ...
3
votes
2answers
291 views

Performance of Postgres VIEW published in GeoServer

Does anyone know what will be the performance difference between publishing a spatial table directly vs. creating a view of the same table and publishing it in GeoServer? Does the spatial index on a ...
1
vote
2answers
444 views

Can't read returned geojson

I've got a function that takes an Openlayers mouse click and puts the click location into a SQL statement which then gets passed to a JSP script which runs the SQL statement on the PostGIS database ...
4
votes
2answers
1k views

Rendering geoJson polygons in google maps android apiv2

I'm in the process of publishing spatial data stored in a postgis database to a google maps based android app and am trying to put together a plan for rendering my data in the most efficient manner ...
0
votes
1answer
718 views

SELECT assign_vertex_id('ways', 0.00001, 'the_geom', 'gid'); not work

I'm using using OSgeo 6.5 dvd and studing pgrouting workshop (link) for get shortest path. I created network topology call "pgrouting - workshop". There are three commands to execute in section 5.2 ...
6
votes
1answer
167 views

Database Diagrammer for PostGIS [duplicate]

Is there a application, or QGIS plugin to generate database diagrams (specifically for PostGIS) similar to the ESRI Diagrammer? Thanks...
1
vote
0answers
163 views

How can I combine these two commands? (Get a clipped raster ASC-II file from PostGIS)?

My objective is to clip a polygon from PostGIS raster table, and then save this raster (draw polygon shape) as an ASC-II in a folder. What I did now are 2 steps. 1st Step: Insert the clipped raster ...
5
votes
3answers
787 views

Create a network topology for pgrouting with grade separation

I have built a network using Ordnance Survey's ITN data and I can solve routes across the network. What I need is to build in grade separation topology for overpasses and underpasses on the network so ...