PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
2answers
113 views

How to select 200 random points within a 100m radius in Postgis

I'm currently learning to use PostgreSQL and PostGIS. I would like to know how to write the SQL that would allow me to select 200 random points within a radius of 500m from a user defined centre point....
1
vote
1answer
83 views

postGIS, length, area, density of line network

I am trying to figure out the length and density of all major roads and minor roads within a study area to return something like: |STUDYAREA|a_road_length|a_road_density|other_road_length|...
1
vote
1answer
52 views

raster2pgsql size problem

I am about to import a raster (.tif with 50mb, size 3301x21676, pixel size 10x10) into postgres with raster2pgsql. With small extracts of the image it works but ... raster2pgsql [myfilename].tif -F ...
4
votes
1answer
93 views

Weighting amount of overlapping polygons in postgis

I have two tables that I join in my existing workflow. One of these includes flood events (was a shapefile that has been imported to PostgreSQL), the other one is a grid that holds the amount of ...
1
vote
0answers
42 views

Read latitude/longitude to create new point grid at higher resolution

New to SE, needed to reformat my other question. Basically I'm looking at reading a CSV with longitude and latitude points (in this case housing exposure, simplified below): HUC4,Longitude,Latitude ...
0
votes
2answers
82 views

Publishing Map on ArcGIS Server Manager without using ArcGIS for Desktop? [closed]

I want to publish my maps on sever manager. My map data is in PostgreSQL and I want to connect it without using ArcGIS Desktop. Is there any possibility to publish service without involving ArcGIS ...
0
votes
1answer
135 views

postgis/postgres ST_Distance and GROUP BY

Trying to find the nearest road to a postcode point and order the results by distance. The roads are named according to UK convention so A606 etc and there are multiple entries for many road numbers (...
0
votes
1answer
42 views

Service Definition File (.sd)

Is there any possibility to make a SD file without using ArcGIS Desktop? I have to upload my service on a service manager and I am using PostgreSQL as a database.
2
votes
1answer
67 views

Group by Location within N Meters in PostGis

In PostGIS, given several Location objects, each of which looks something like this: { :id => 1, :latitude => 32.4196, :ll_point => "(-10111.1877176122, -5384086.44077608, ...
1
vote
0answers
44 views

Updating an Openstreetmap PostgreSQL database after the system restore

My Openstreetmap PostgreSQL database takes a long time – about 25 hours by now – for updating via Osmosis after I had made a system restore at the last weekend. Before making the system restore it ...
3
votes
0answers
67 views

Layers created by GeoServer do not display in Mapbender3

I have installed GeoServer 2.7.2 on my local windows 7 machine, which retrieves data from a remote PostgreSQL server. The data is represented in layers just fine! I also have installed Mapbender3 ...
0
votes
1answer
38 views

Relate two tables based on geom

I have a problem, which is, I have a table ways (like in the Workshop of pgRouting) and I also have a different table street_name, where it contains the name of the street (among other information). ...
3
votes
0answers
54 views

Enumeration in QGIS

When I am using QGIS 2.8.3. I want to use a series of predetermined set of texts, such as 'wood', 'brick','concrete'. I have created an enumeration file in SQL, I do want to link that my existing ...
2
votes
2answers
52 views

Distance between sequential points postgre

My table is as follows 1;"2015-10-02";"POINT(lat,lon) as geometry" 2;"2015-10-03";"POINT(lat,lon) as geometry" 3;"2015-10-04";"POINT(lat,lon) as geometry" How can I find the distance between two ...
1
vote
1answer
45 views

Multiple queries in pgrouting (length as result)

I am facing the following problem with two tables: The first table contains all my streetnetwork with cost, source, target. Another table contains 60 nodes (from the above network). Between all ...
1
vote
0answers
60 views

ESRI/SRID mismatch: How to correct?

I'm currently working with PostgreSQL and CartoDB to display X and Y coordinates converted to a geom. My shp files have been created and are displayed through CartBD. When I try and run a map view, ...
1
vote
1answer
39 views

Is there any site where I can found city shapes in postgreSQL polygon format

Is there any site where I can found city polygon area (for postgreSQL or similar)? I don't know if such resources exists, and I don't know how to find it.
0
votes
1answer
67 views

postgresql out of memory [closed]

I clip 70 raster with polygon in postgresql, the result are: out of memory, Fatal on request size of 10872 I read some solution about changing the postgresql.conf parameter but nothing work for me ...
1
vote
1answer
79 views

PostgreSQL is refusing connections from MapServer

I have MapServer 6.04 running in Centos 6.7. I am able to server shapefiles and GeoJSON, but when I try to connect to a PostGIS (2.1.8) enabled PostgreSQL (9.4.5) database it fails with the error: ...
0
votes
1answer
85 views

How do I create and manage custom SQL functions in CartoDB?

I'm working through a CartoDB tutorial at the moment, and got to the point of creating a custom SQL function. Do I just write this in the "CUSTOM SQL QUERY" Field on the right of my table? How do ...
1
vote
1answer
104 views

Inserting spatial data into existing postgis geometry column from shape file based on ID

I have an existing postgresql database table "city_data" with Non-spatial data in the following format: City_ID as varchar, City_name as var_char, pop as int,.... I have enabled the postgis ...
2
votes
1answer
193 views

How to use ST_DWithin to query features from one table within a distance of a specific feature or features in another table

I'm pretty new with PostGIS. I have a parcel layer and a census block layer in a postgres database, and I would like to be able to query out all census blocks within a specified distance of a specific ...
2
votes
0answers
42 views

Efficient processing of ST_ValueCount Results in Postgresql

General Problem: I have a table of rasters. The rasters are classified MSI images, so each pixel is an integer indicating the class of the pixel. For a number of regions, I am querying pixel counts in ...
1
vote
1answer
50 views

Deleting records from table where ST_DIstance from another record in another table is greater than a certain amount

I'm currently learning SQL on the run, and have come across a situation where I'm trying to delete records from a table depending on a function result. I can select the records of interest with the ...
2
votes
1answer
67 views

How to find records using a distance filter, in a table of Points?

My table fields are: POSIC (id, geom, dt_inclusion). DB is PostGIS. Data comes from a GPS Source, then i can create a route ordening the points by Date_Inclusion. So, lets say i have registries A,B,C ...
0
votes
0answers
43 views

postgis ST_Intersect on 2 valid geometries throws TopologyException

Using Postgis 2.1 on PostgreSQL 9.3.4, I'm getting an error when I try to intersect two valid geometries: TopologyException: found non-noded intersection... These geometries are created on the fly ...
1
vote
2answers
111 views

How to aggregate pgr_dijkstra results by attribute?

I working with a table with a lot of points per road/street. I'm using pgRouting's pgr_dijkstra method to give me a path, but it returns too many points per road/street, I'd like to know if there is a ...
1
vote
2answers
56 views

PostgreSQL/PostGIS: Closest outside point (health center) to point (education center) inside polygon (danger zone)

I am trying to find the closest health centers (points) to the education centers (points) located within the "danger zone" (a specific polygon) while excluding those health centers located in the "...
1
vote
1answer
48 views

How can I count points from several buffer distances in PostGIS updating the table?

I have a problem regarding a calulation on PostGIS. I have to calculate how many facilities fall within a given distance. This is not really a problem though, but updating my table is. I have this ...
2
votes
2answers
151 views

PostGIS operations extremely slow when called in a script

OK, I am aware that this is rather vague, but unfortunately I was not able not reproduce my problem in simplified code. I am running a series of functions within another function (main()). All ...
2
votes
0answers
78 views

Dijkstra's Algorithm direction issue

I am trying to implement Dijkstra's algorithm, my query is working fine if I set direction parameter to false. I have to consider the direction of travel while calculating route from point A to point ...
0
votes
1answer
119 views

Restoring public.layer_styles table from PostGIS dump

I created a PostGIS database on a PostgreSQL server and stored some QGIS styles (in public.layer_styles). When dumping and restoring the database only this table gives me an error and stays empty. I ...
0
votes
1answer
88 views

How to add tiger schema to the database search path? [closed]

The tiger schema must be added to the database search path for the functions to work properly. I just started recently to use tiger dataset and postgres database and I have basic understanding of ...
6
votes
2answers
239 views

How to do a spatial join in PostGIS that returns on the minimum value of point intersecting polygon?

I have a polygon table (buffer) and a point table (dem_points), and I want to do a spatial join on them. I need to join the point with the minimum elevation (dn) to its spatially corresponding ...
1
vote
1answer
112 views

Can't upgrade to PostGIS 2.2

I am running on MAC OSX El Capitan. My version of postgres is 9.4.5.0. My PostGIS version is 2.1.7. I cannot upgrade to PostGIS 2.2, because when I am trying to do, it pops "PostGIS requires SFCGAL >= ...
2
votes
2answers
45 views

PostGIS - raster and bands

I have several images from different times and for every time I have three types of image (temperatureA, temperatureB, pressure). Should I store them as every time as band in a single raster with N ...
0
votes
1answer
150 views

TypeError: a.geometry.getBounds is not a function in OpenLayers.js (line 611, col 334)

I'm using OpenLayers 2.13 and Postgresql 9.3 for my development activities. I want to load region data from database and load into openlayers. Below result i'm getting from St_astext(geom) in ...
3
votes
2answers
688 views

How to enable SFCGAL in PostGIS?

I have the problem with the enabling extension SFCGAL. CREATE EXTENSION postgis_sfcgal; doesn't work for me even I have the PostGIS v2.2. When I do that, it throws an error ERROR: could not ...
0
votes
1answer
78 views

Generating pivot table in PostgreSQL that ties cities to counties using QGIS (or PostGIS)?

I have a cities shapefile and a counties shapefile. I have the counties shapefile imported to a PostgreSQL table. I haven't transferred my cities to PostgreSQL yet, but the issue I really want to ...
1
vote
1answer
72 views

How to get a list of all objects contained in my different QGis layers

I would like to know how we could do in QGis to get a list of object contained in my different opened layers: We have n .SHP files or PostgreSQL Views (they all contain at least an "object type" ...
1
vote
0answers
57 views

refine nearest_node with radius-distance ( ST_Distance, ST_DWithin )

i am a hobby-IT-person and currently stuck with some simple problem, maybe somebody can help me: This is my working POSTGIS-function, source by underdark: ALTER TABLE distance_graz ADD COLUMN ...
1
vote
1answer
147 views

Join two tables completely on geometry (postgis)

I have two tables with the same street network as geometry (but not a same id). What I want to do is to join both tables with all attributes to one for all geometries which overlap exactly. ...
2
votes
0answers
37 views

Creating lines from lonely nodes in a linestring table to closest point on another linestring table

I want to segmentate a country (denmmark) by using barriers such as larger roads, railroads and the border/ coastline as the delimiting lines of the polygonise function in QGIS. But as most roads and ...
2
votes
0answers
42 views

Changing border style in OpenStreetMap?

I am trying to figure out how to change OpenStreetMap country borders color/thickness. I've set up a tile server using OSM data, PostgreSQL & Mapnik. The default mapnik style is used. There ...
3
votes
2answers
345 views

Is there a way to change ST_asGeoJSON return values?

The function ST_asGeoJSON returns something like: "st_asgeojson": { "type":"MultiLineString", "coordinates":[ [ [-3.13512629299993,39.7174353130001], [-19....
3
votes
2answers
96 views

PostGIS does not find intersection between line and polygon

I have a line and polygon: I would like to programmatically figure out whether they intersect or not. So, I do: select ST_Intersects ( ( select ST_SetSRID(ST_Buffer(ST_Transform(...
0
votes
0answers
34 views

Number of rows returned via `shp2pgsql` for a given shapefile

With the shp2pgsql tool, I see it converts a shapefile into a SQL statement. Upon closer examination, does the SQL generated ALWAYS contains 1 insert statement for 1 row? Or are there instances where ...
1
vote
1answer
30 views

counting values in an update statement postgres [closed]

I have a repetitive method I want to execute and I expected underneath script to work. I found out the count() function is not allowed in postgres update statements. What is the most logical and ...
2
votes
2answers
145 views

QGIS Web Client - postgreSQL Layers wouldn't shown

since updating my QGIS to version 2.8.3 the postgreSQL Layers wouldn't shown in the QGIS web client. In the Desktop version the project works very well I only can see the projecttitle and the groups ...
1
vote
1answer
81 views

OpenStreetMaps: what are the !pixel_width! and !pixel_height! in the queries

The Github repository containing the CartoCSS styling for the OpenStreetMaps dataset also defines queries for the different layers (see this file). An example of such a query is SELECT way, "...