Tag Info

Hot answers tagged

8

you have to use the union function like this SELECT att1, st_centroid(st_union(geom)) as geom FROM schema.table GROUP BY att1; so you can obtain centroid of point that have same attribute.


6

ERROR: **function addGeometrycolumn**(unknown, unknown, unknown, unknown,unknown, integer) does not exist It seems that PostGIS is not yet installed. PostGIS is an extension of Postgres which allows the use of geographic files. Install it and your import will work fine.


6

I am answering my own question with a proposed query. select *, ABS(x_permit-x_station)+ABS(y_permit-y_station) as manhattan FROM (SELECT longname AS NAME, lines AS metadata, T .slug, ST_Distance ( T .geom, ST_Transform (P .geometry, 3435) ) AS distance, ST_X(ST_Transform(p.geometry, 3435)) as x_permit, ST_Y(ST_Transform(p.geometry, 3435)) as ...


5

First of all I suggest to use postgis function ST_SimplifyPreserveTopology instead of qgis semplification. If I understand it right you want to have a small file with an high level of detail... and thats impossibile (if you still want to use shape files). But you can do what other webgis do. You can create 2 or more shape files with different semplification ...


5

And, after a bit more reading the ogr2ogr help I found the answer more easily than I expected. ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb -sql "SELECT * FROM table" -dialect spatialite -nln new_table EDIT: As suggested by user30184 in the comments a cleaner, simpler method is: ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb ...


3

I use cygwin all the time. However, I elected to use Starting PostgreSQL in windows without install and the binaries from EnterpriseDB and PostGIS. That allowed me to push the limits with recent developments. That being said, Cygwin, MS Windows, Linux, and Mac are just database clients or database servers. You'd want to follow some sort of configuration ...


2

ST_Distance (folowing the documentation) - For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. For geography type defaults to return spheroidal minimum distance between two geographies in meters. So - if you'll feed it with geometry,4326 you'll get distance in units of 4326 ...


2

Yes it is just the same. Web Mercator goes by 3 different srids (900913 and 3857 being two of them). I'm puzzled you don't have it. 3857 as I recall has been packaged with PostGIS for quite some time. Which PostGIS version are you running? SELECT postgis_full_version();


2

There shouldn't be any reason why you can't run a Frequency on the attributes of a 12m feature shapefile with ArcGIS, but the SQL query equivalent is a basic GROUP BY: SELECT org, protocol, map, count(*) Cnt_map FROM table1 GROUP BY org, protocol, map ORDER BY org, protocol, map You could even order by the frequency in the computed column so that most ...


2

Looks like there is an error in the geography(bytea) function that is bound to the bytea to geography cast. You can patch it temporarily like this: CREATE OR REPLACE FUNCTION geography(bytea) RETURNS geography AS '$libdir/postgis-2.1','geography_from_binary' LANGUAGE 'c' IMMUTABLE STRICT; Should be fixed in upcoming releases.


2

I'm assuming the ID field is what uniquely identifies the person. BTW, really bad to use * especially since Table1 and TAble2 have at least ID in common. SELECT Table1.* FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE Table1.Date = '2008-11-04' AND NOT EXISTS ( SELECT Y.ID FROM Table1 As Y WHERE Y.ID = Table1.ID ...


1

This function code could be (it's an example - did not check if it's working) : CREATE OR REPLACE FUNCTION give_me_cost(source integer, target integer) RETURNS decimal(8,2) AS $BODY$ with subset as ( SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra( 'SELECT gid as id, source, target, st_length(geom) as cost FROM roadnetwork', $1, $2, ...


1

Perhaps you may need to think through the data type for sites. What is it for? It seems that geometry is defined as geometry(PolygonZ,4326), which I think is a bit silly. A polygon could be expressed as a 3D object if all the linear rings are coplanar (like a triangle or a lake). However, if it were, for example, the border of Nepal, why keep elevations ...


1

You can use St_GeomFromText to add geometries in WKT format UPDATE sites SET geometry = st_geomfromtext('POLYGON((0 0, 1 1, 2 2, 3 3, 0 0))') WHERE ID = 1123


1

You may want to use the ST_Envelope() function. Specifically, see the 2nd example using a LINESTRING geometry. Given a LINESTRING composed of two vertices that represent your points, you should be able to get back a Polygon geometry which might be more useful to you.


1

You should use rgdal, specifically writeOGR (which is for vector data) to write the table to Postgis. Note that you must have a spatialdataframe as designated by the sp package (autoloaded with rgdal), I suspect your results of rasterToContour are the correct format. #R code assumes library(rgdal) writeOGR(x, "PG:dbname='BDDMeteo' user=user ...


1

Hey first you should try to dump your database using pg_dump (http://www.postgresql.org/docs/9.3/static/app-pgdump.html) before you do anything else. This at least should give you a copy of the current non working data, which you can revert back to. Also make a copy of your data dir ...


1

Probably the first thing to do is to import your data again after you have read about addresses in OSM from http://wiki.openstreetmap.org/wiki/Addresses. Why you should do import again is that the default osm2pgsql style file does not import all the keys which will likely need, for example addr:street ...


1

The ST_ConvexHull function is implementing the Simple Features specification portal.opengeospatial.org/files/?artifact_id=13228 which says that "simple features are based on 2D geometry with linear interpolation between vertices". Therefore you get a flat polygon as a result.


1

So, you first need to create the function the trigger will fire. In a rush sorry, here is another thread with an example: http://stackoverflow.com/questions/13753320/creating-triggers-and-functions-in-postgresql Create the function that returns the trigger and then the trigger itself attached to the table. CartoDB runs on top of PostgreSQL 9.3, so if you ...


1

This message appears to be a generic failure message and does not mean that there was a permissions problem. In my case the layer that I was trying to save the style of was composed by a complicated sql join and QGIS tried to use the whole sql query as the table name resulting in it overflowing the table name field. It really should check -- I guess I will ...


1

I don't know what the magic in table2 is all about. However, your x looks like '2008-11-04' and y looks like '2012-11-06'. If your dates have a time component in them, then you may be failing to match on date i.e. 2011-11-06 may have a time value of 00:00:00 or midnight while the dates in your table have 2011-11-06 16:35:33. Those two dates are not equal. ...


1

Firstly few questions: 1. what do you mean by converting roads to nodes? Maybe you meant network graph? 2. Are you able to find at least one way and the trouble is with many ways? 3. Nearest school euclidean or by the roads? 4. What was the work-flow of this conversion form roads to nodes? 5. What are the names of tables and column names in this tables? 6. ...


1

To building your graph you could try using ltree, a graph data type for PostgreSQL, then creating a compound gist index that contains both the geometry and ltree columns.


1

I don't think the issue is with the ST_GeomFromGeoJSON call. It's possibly the id field you have OR your table structure. Can you provide your table structure. SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}'); alone works fine, so doubt that is your issue. If the above query fails, then your postgis install is corrupt in ...


1

You did not change the local line, it's peer mode. If you want to use md5 auth instead, you must configure md5 auth, e.g. local all all md5 instead of your current: local all all peer


1

First thing is that you're creating postgis extension on whole database not on single schema. Second thing is you set the search_path if you don't wont to write schema name before every tablename, so notation 'different_schema.field_polygons' will work without setting patch. I think the trouble is somewhere else... You're trying to create shapefile with only ...


1

You mention PostGIS. If you want to use PostGIS, with the spatial functions and spatial index that comes with PostGIS you will have to use the PostGIS geometry or geographic data type. If you stor latitude and longitude in separate columns you will have to reinvent the wheel to do any spatial calculations. And a 2d point consists of longitude and latitude ...


1

Benno, you can use Spatial Manager Desktop, the Professional edition can read and write the formats you said(among others), and also supports the execution of tasks from the command line. You can download a trial version (limited), here: http://www.spatialmanager.com/downloads/ Hope this helps Disclaimer: I work for Opencartis (Spatial Manager ...


1

Im guessing the problem might be that your filtering contour lines at the rendering phase, instead of when fetching them from the database. The best strategy for making mapnik/tileMill run fast is to never fetch anything from the database that isnt going to be rendered. Here is the sql query I use for contours: (SELECT geom, height, idx FROM contours ...



Only top voted, non community-wiki answers of a minimum length are eligible