Tagged Questions

PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
1answer
66 views

Inserting union of two tables into another table Postgresql

I am working on a research project. I have two tables, one of a polygon that represents a severe weather risk area, one that represents the US. I want to merge them into one output polygon. I am stuck ...
3
votes
1answer
91 views

Change from polygon to multipolygon in postgres

can somebody describe please how to do the transformation from polygon to multipolygon in the postgres DB, is not possible for me to save my edited data. All data loaded just need to do some editing ...
1
vote
1answer
30 views

Does the psql prompt have a working directory in the file system?

I am learning postgis. This tutorial says "Navigate to where you downloaded the shapefile." Does that mean navigate to this location in the #psql prompt or navigate to this location with the $propmt? ...
1
vote
1answer
44 views

PostgreSQL : question about SELECT .. WHERE .. = max()

If you have, for example; SELECT * FROM data WHERE line = min( (SELECT length FROM lines) ) AND number < 500; (It's just an example, not a real code) How does PostgreSQL react when the ...
2
votes
0answers
31 views

PostgreSQL : Question about dropping tables inside funtions

I am writing a PostgreSQL code where I have a function wich is used multiple times in a single query with different input values. Inside the function I create some tables and use them. What is the ...
0
votes
0answers
223 views

Problems moving POSTGIS database from postgresql 8.3 to 9.3 using Navicat

I am using Navicat 11 to move a POSTGIS database from 8.3 to 9.3 on Mac OS X 10.9. I have installed PostgreSQL 9.3 and PostGIS with homebrew (brew install postgresql postgis) and everything looks ok. ...
1
vote
1answer
186 views

PostGIS Type Modifier Error

I'm trying to work an example from the "PostGIS Cookbook" and I'm getting an error I don't understand. I'm running the example using Postgres.app on a MacBook. Postgres.app is running PostgreSQL ...
0
votes
0answers
32 views

PostGIS : check if 2 lines are at contrary sides of third line

I am looking for a workflow to select a line on the condition that it is located on the contrary side of a middle line. So there are 3 lines (2 are given, 1 I want to select). I don't need you guys ...
4
votes
2answers
170 views

How to speed up a query for nearest linestring to point data

I have processed a Strahler stream order layer with a lot of river linestrings (type: Multilinestring) and their corresponding strahler number (see information here). Besides I have a point layer. I ...
1
vote
0answers
68 views

Do ArcGIS clients that direct-connect to postgresql/postgis databases override configuration settings?

I went in to modify some settings on a Postgresql database via the PGAdmin config editor and some of the settings don't have their default values and they are higher because they've been overridden ...
2
votes
1answer
119 views

PostGIS : I want to use ST_OffsetCurve with MultiLineStrings

I need to use the function ST_OffsetCurve for a multilinestring, but PostgreSQL gives me the error that the function is only for Linestrings. Is there a way arround? I want to use the function ...
1
vote
1answer
104 views

CartoDb - Aggregate and Infowindow

I'd like to use CartoDb to map historic addresses of a city (link to demo). Individual addresses are mapped as points on the map. Detailed information for each address point should be displayed in the ...
4
votes
2answers
115 views

pgAdmin vs psql

I have downloaded postgreSQl 9.3 and Postgis 2.1 recently as an educational adventure and have been working with both through the pgAdminIII interface. I haven't used psql yet when working with my ...
2
votes
1answer
111 views

Update postgres/postgis point data attribute from another enclosed polygon shapefile in QGIS

I have a placename point table in postgis with 'district' column. a districts polygon shapefile with district name in 'Name' column. See the image below. I would like to update the 'district' ...
0
votes
2answers
231 views

GeoServer create layer from PostGIS (data type point)

(Several days ago a user on gis stackexchange suggest that I should use OpenGeo (I was using geojson before). I succeed at what I was doing at that time, but now I have a different problem.) I was ...
1
vote
2answers
81 views

Is it possible to run a spatial query in QGIS 2.0 across multiple layers using a user-specified radius from a point?

I would like to be able to click on a point on a map to select all vector features (from multiple layers) within a specified radius of the point. Can anyone point me in the direction of how to do this ...
1
vote
0answers
53 views

How to define Z dimension on table with PostGIS for import in TileMill?

I'm trying to reproduce the idea behind this blogpost from Mapbox, but with other data, for example the ne_10m_populated_places_simple dataset from Natural Earth. By adding a z-dimension to the data ...
2
votes
1answer
71 views

Length of a line within a surface

I need to find out the lengt of each line within a surface. I have approx. 20'000 diferent lines and around 10'000 different surfaces. what kind of SQL-sentences do I need to use? I've tried things ...
2
votes
1answer
55 views

ST_ZMax() does not exist in PostGIS

I'm trying to follow this example on Mapbox blog, about using 3D shapes in PostGIS. The database-query is this: DROP TABLE tilemill_buildings; CREATE TABLE tilemill_buildings AS SELECT ogc_fid, ...
3
votes
1answer
193 views

How to create a buffer on a linestring with varying or increasing/decreasing width

I want to buffer rivers/streams for a topographic map. I'm using a PostgreSQL/PostGIS database. Usually, I create buffer with ST_Buffer with a constant width. For example, the width of a river buffer ...
1
vote
0answers
275 views

QGIS loads PostGIS layers but DBManager can't connect

DB Manager can't connect to PostgreSQL, but "Add PostGIS Layer" can. My eventual goal is to save created layers to PostGIS. I assume I can do this using the DBManager Tool. I can alternatively use ...
2
votes
1answer
93 views

PostgreSQL : total length of roads in polygon

I have 2 tables : wgo (lines) and wbn (polygons) With this code, I can display the length of each road inside a polygon. If I have a polygon with 2 roads in it, as a result I get 2 rows with the 2 ...
2
votes
1answer
277 views

PostgreSQL : finding all lines intersecting with a polygon

I am totally new in the world of GIS but as a student surveyor I am working on a project using postgreSQL and PostGIS. I have one database with all the roads of my country (lines), and one database ...
3
votes
2answers
268 views

Postgres Log Error

I am using Geoserver + GeoWebCache + Postgres for an application. On the application side there is no bugs but on postgres I am getting this log. Please explain and help me to solve this ...
0
votes
0answers
73 views

pgrouting shortestpathfromAtoB multiple pairs

I've got to solve a shortest path problem between multiple location pairs. E.g.: A to B C to D E to F etc. These pairs are stored in a table routing_basis like hkey; A; a.lat; a.lon; B; b.lat; b.lon ...
1
vote
0answers
151 views

How do I catch PostGIS/PostgreSQL RAISE messages in QGIS?

I have a trigger set up in PostGIS/PostgreSQL which will show a RAISE WARNING when an inserted or updated polygon is within a certain distance of some predefined planning constraints. I want this ...
1
vote
3answers
113 views

How to set the tolerance value in pgr_createTopology function of pgrouting?

I am making a map based on the floor plan of a building. I made the shapefile and added it to the PostgreSQL database. I created the topology for the map using the following code: SELECT ...
1
vote
1answer
116 views

PostGIS ST_Within returns zero data

I am using PostgreSQL 9.3.3 & PostGIS 2.1.1 and I have a table Entries which has a column called "latlng" of geographic type (this column stores entry's latitude and longitude coordinates). I am ...
0
votes
3answers
203 views

Faster routing algorithm on pgrouting [duplicate]

I have downloaded data for india as a dbf file then converted it using osm2po to make it routable but the problem is when i make any routing query it takes a lot of time around 300 seconds and these ...
2
votes
2answers
121 views

how to Find n nearest lines for the given points from a collection of lines?

I have two tables, points and lines. For each point in point table, I want to find out nearest 3 points in the nearest 3 cables. I almost developed a query, but it is best suitable for single point, ...
2
votes
1answer
238 views

Postgresql table won't display in QGIS 2.2

I just downloaded QGIS 2.2 to check it out and now one (just the one) of the tables from one of my PostgreSQL database won't display. It loads just fine in 2.0. The records are all there (there's ...
0
votes
0answers
42 views

Speed drastically reduced when including geometry, is there a way to improve?

-- EDIT: Issue is that the shape files being used are at too small of a precision and way too large, output was 488M total, looking for different source -- I'm having some speed issues with a complex ...
7
votes
1answer
159 views

Replacing a corporate GIS [closed]

I'm looking at replacing our corporate GIS, this is the first time that I have had the responsibility of such a task despite my years of experience with the software as an end user. We will be ...
2
votes
2answers
85 views

self intersecting polygon SQl

I am trying to write a sql to query one land parcel for intersections between itself. I can do this fairly easy in QGIS using topology checker but i don’t know why this does not produce the correct ...
1
vote
1answer
110 views

Monitoring Geoserver for Production Environment

Is there any opensource server monitoring is available for Geoserver ? Like watchdog and nagios? Suppose if geoserver is stopped working whether mapmeter will automatically start it..Like the ...
0
votes
0answers
21 views

Postgres self self intersecting polygons and error

I have a land parcel which has self intersecting polygons and I am trying to create a view to select all the distinct polygons but sql query does not generate correct results. The polygon has 500 ...
2
votes
1answer
72 views

How to make osm2pgsql import a tag?

I loaded OSM data into PostgeSQL with Osmosis&Osm2PgSQL, and see many tags are missing. I need to make it import lanes tag. How can I make it save this tag too? edit: my solution was to copy ...
1
vote
1answer
67 views

Problem with runing a loop in a query in PostGIS

I am new to PostGIS and I have a question which can't solve. I have a table of buildings each having a value for number of people living in it. I would like to calculate the total value (sum of number ...
1
vote
1answer
120 views

Problem with nested query in PhP and PostGIS

I have a simple problem regarding a nested query. I would like to select all buildings in a given circular buffer and create a kml file of those selected objects. The following query does the job very ...
2
votes
3answers
135 views

Problem with result of query in PostGIS

I run the following query and irrelevant of the point coordinates and radius I always recieve all the records of the table I'm querying. Can anyone point out what the problem could be with this query? ...
4
votes
3answers
2k views

Installing Postgis on Ubuntu 12.04: unmet dependencies libgdal1

After sudo apt-get install postgresql-9.1-postgis I got that massage: The following packages have unmet dependencies: postgresql-9.1-postgis : Depends: libgdal1 (>= 1.9.0) but it is not going to ...
-3
votes
2answers
74 views

How can i create the spatial queries in QGIS? [closed]

please, Help me. i'll really appreciate your answers.
3
votes
3answers
174 views

Open Street Map: converting to country -> administrative unit -> city database

To moderators: If you think this question is too broad, please point me into right direction before closing, I'm desperately looking for help. I'm stuck on my task and I don't know what to do. The ...
1
vote
1answer
208 views

ST_Transform not converting from utm to lat/lon

This line: ST_AsText(ST_Transform(the_geom, 4326)) Returns the "coordinates" as utm coordinates. Although, I thought ST_Transform was supposed to convert the coordinates from utm to lat/lon. I ...
-1
votes
1answer
73 views

plypython set-up

I am trying to set up plypython on the following system: Windows 8.1 PostgreSQL 9.2 64-bit PostGIS 2.0 Python 2.7.05 I have added plpython2.dll to \PostgreSQL\9.2\lib When I run: CREATE ...
3
votes
0answers
77 views

How do I simplify the straight sections of (multi)Linestring in PostGres/PostGis leaving the curved sections untouched

I have (multi)linestrings containing upwards of 5000 points and I want to perform two simplifications possibly using Ramer Douglas Peucker or alternative methods in PostGis The (multi)linestrings ...
0
votes
0answers
38 views

display route from database(postgresql) on

For first part: Link i have postgresql9.1,postgis2,pgrouting2 and qgis2.2 this is the second part of what i need to do: Search in a database (postgresql with pgrouting) for a line from public ...
2
votes
2answers
237 views

Problem in running query on PostGIS using PHP

Would you kindly help me solving this problem. I'm trying to use the PHP code provided here in order to read geo-data from PostGIS. The code seems clear without any problem (also nobody has ...
2
votes
1answer
188 views

How to write a new layer to PostgreSQL from QGIS plugin

The PyQGIS cookbook states that QgsVectorFileWriter can write vector layers to any format supported by OGR: https://www.qgis.org/en/docs/pyqgis_developer_cookbook/vector.html#writing-vector-layers ...
1
vote
1answer
275 views

Most Efficient method of Storing Point Data in PostGres/PostGIS to later re-create a track

I am trying to determine the most efficient way to store point data in PostGIS to later re-create tracks Criteria: 1 second updates 100- 200 tracks active at any one time 1 hour max ...