Tag Info

Hot answers tagged

14

My guess is that you coordinate transformations have introduced tiny rounding errors (see an example below). As there is no way to set the tolerance in ST_Equals, this is causing ST_Equals to return false for some geometries that only differ in the nth decimal place, as the geometries have to be identical in every respect -- see the intersection matrix ...


12

I just had the same problem on Ubuntu Server 14.04. I installed postgis extension from the official Ubuntu repositories using apt-get install postgis. Then, find /usr -name postgis.control didn't return any results. The reason was extension/postgis.control wasn't installed because postgis-scripts wasn't. $ aptitude search postgis i libpostgis-java ...


9

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.


8

ST_Distance actually calculates the distance between all the pairs of points, so, as such, no index could be used. So your query will do a sequence scan and then choose those geometries that are less than the distance you specify away. You are looking for ST_DWithin, which does use an index. SELECT SUM(population) FROM points WHERE ST_DWithin(location, ...


7

Why use a database? Because it's not necessarily the case, especially with larger datasets, that you can expect to be able to push the entire thing to the client. If you're talking thousands of points, then sure, but for millions of points you probably don't want each and every one represented in RAM on your end users' client. Not everyone has a super fast ...


6

In Ubuntu 14.04 you also need to install the postgresql-9.3-postgis-scripts package. After I ran sudo apt-get install postgis postgresql-9.3-postgis-scripts I was then able to successfully run CREATE EXTENSION postgis; in my database to initialise PostGIS.


6

There's two things going on here: the GIST API in PostgreSQL and the bindings of types to that API for the purposes of building an R-Tree. PostGIS necessarily uses the PostgreSQL GIST API. That's what it's for. That way we don't have to worry about transaction management or writing things to disk or all the other messy important things involved in ...


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 ...


6

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 ...


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

A really easy, but not fantastic measure is to get the Hausdorff distance between each combination, which is done with the ST_HausdorffDistance function. Using approximate LineStrings from your figure, these are all shown in blue, and the Hausdorff distance is shown for one of the pairs of lines in red: And the query to sort the 6 combinations in ...


6

Save your Excel as a comma-delimited file, then: COPY your_table FROM '/path/to/csv/file/data.csv' WITH DELIMITER ',' CSV HEADER; Another option would be ogr2ogr. Edit: after some more searching, the answers to this question should help.


6

Ironically, the fastest way to find the set of things not within other things is to do a full join that finds the contained things, but using a LEFT JOIN, so the un-matched things are hanging about to be found, thus: SELECT pts.* FROM pts LEFT JOIN polys ON ST_Contains(polys.geom, pts.geom) WHERE polys.id IS NULL; The un-matched rows in a left join are ...


5

Your SQL query is missing one function. Add ST_GeomFromText and it will work. SELECT ST_AsText(ST_Centroid(ST_GeomFromText(('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6 )')))); Result: POINT(2.307692 3.307692) If you have a point table "mypoints" the corresponding query is select ...


5

Your computer should be fine for importing Europe. Given your dataset size and computer, I'd recommend something like this osm2pgsql -c -S /usr/share/osm2pgsql/default.style --slim -d osm-europe --flat-nodes flat-nodes.bin --number-processes 8 -C 20000 europe-latest.osm.pbf I'm assuming that you have an 8 thread CPU, if not, adjust --number-processes. ...


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

Since you're not worried about simultaneous edits on features, I'd say that in theory you have nothing to worry about. The main danger w/ QGIS is that simultaneous editors can stomp on each other's edits without noticing ("last edit wins"). For data under active editing with multiple users you might want to at least keep track of history, which you can do ...


5

If you don't have an attribute, you should first find a polygon feature class with the boundaries of Greece (e.g. on gadm.org). Then you have two solutions : You want the roads to be cut at the boundaries of you area of interest : use some clip tools (in QGIS : Vector -> Geoprocessing -> Clip ) You want to keep the two sides of a road when it crosses a ...


5

Your data looks like it's Mercator (big negative X's). I'll assume web mercator, but that could be wrong, and may lead to meter-level inaccuracies (if it's "real" mercator, use 3395). shp2pgsql -c -d -D -W LATIN1 -s 3857 -I 'path/to/shapefile.shp' my_table | psql -d mydb Now flip the coordinates to geographics inside the database ALTER TABLE my_table ...


5

First of all, some performance metrics, comparing the two different ways of producing points for a random selection of a million points. create table test (id serial, x real, y real, geom geometry(POINT, 27700)); insert into test (x, y) select random(), random() from generate_series(1, 1000000); update test set geom = ST_SetSRID(ST_MakePoint(x, y),27700); ...


5

You can simplify the whole thing by using coalesce, which selects the first non-null item in a list, in this case, either the nearest point to you search point, (28.959495,41.019913), or the point itself. with input_geom (geom) as (select st_setsrid(st_makepoint(28.959495,41.019913), 4326)) select coalesce( (select st_closestpoint(pts.the_geom, ...


5

ST_3DIntersection is available only via the SFCGAL extension. Unfortunately only windows (PostGIS 2.2 experimental) and Ubuntu (via docker ) have it at the moment, so most likely you'd have to compile PostGIS yourself to use it. It would be useful to know the OS you are on. PostGIS windows 2.2 experimental binaries have SFCGAL and can be downloaded here: ...


5

I suspect we don't support it, however you could try with the -m switch where what follows the -m is the filename consisting of old name and new name. It might not work though since gid is an autogenerated field name. If it doesn't feel free to put in a ticket in our ticket tracker http://trac.osgeo.org/postgis/newticket . i think it's a fairly easy ...


5

If that is the query that is used I guess you are out of luck. Is there no WHERE-clause? I have never played with cursors, but as I understand the query, Arc-whatever fetches 1000 rows a time from the whole data set. No matter how efficient and smart Arc-whatever then is to find out what geometries to use and render. All performance is already lost. ...


4

Use a CASE statement and a sub-query: SELECT foo.category, SUM(ST_Length(foo.the_geom)) as length FROM (SELECT gid, year, the_geom, (CASE WHEN year BETWEEN 2005 AND 2014 THEN "1"; WHEN year BETWEEN 1995 AND 2004 THEN "2"; WHEN year BETWEEN 1985 AND 1994 THEN "3"; WHEN year BETWEEN 1975 AND 1984 THEN "4"; ... ...


4

You can't merge those geometries into one linestring and you know why when you have a look at them. OpenJUMP has an easy-to-use "Add features from WKT" tool which suits perfectly for this kind of debugging.


4

I imported a Planet File on a 24Gb Machine (Ubuntu Trusty) with the following .. bzcat planet-latest.osm.bz2 | osm2pgsql --verbose -U YourUser --flat-nodes flat-nodes --keep-coastlines --cache 24000 --hstore --hstore-add-index --tablespace-index pg_default --exclude-invalid-polygon --number-processes 6 --unlogged --cache-strategy dense --extra-attributes ...


4

The ST_Split PostGIS function is probably what you want. To get a single line split by multiple points, you could use something like this multipoint wrapper plpgsql function. I've simplified it just to the "split (multi)lines with (multi)points" case below: DROP FUNCTION IF EXISTS split_line_multipoint(input_geom geometry, blade geometry); CREATE FUNCTION ...


4

EDIT: Update, having belatedly realized this is a Map Server question. In general, to get the convex hull, you will want to group by id/gid, as producing the hull(s) is an aggregate operation. So, without the Mapserver part, the query would look like: SELECT id, ST_ConvexHull(ST_Collect(the_geom)) as the_geom from xyz group by id; Now, to get MapServer ...


4

I think you want to exclude the intersection of the buffer in the where clause. WITH subq AS ( SELECT p.id, p.name, unnest(ARRAY(SELECT q.name FROM w_point q WHERE p.id != q.id AND NOT ST_Intersects(q.geom, ST_Buffer(p.geom, 0.1)) ORDER BY ST_Buffer(p.geom, 0.1) <#> q.geom LIMIT 5) ) as name FROM w_point p ) SELECT ...



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