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.


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

This annoying issue occurs because libgdal was forked between libgdal1 and libgdal1h last year. Ubuntu GIS stable uses libgdal or libgdal1, Ubuntugis unstable uses libgdal1h. GDAL is a translator library for raster geospatial data formats. As a library, it presents a single abstract data model to the calling application for all supported formats. The ...


7

Assuming your data table look like: streetid housenumber 100 12 100 15 101 12 101 18 You could do a basic SQL query: SELECT streetid, count(*) FROM tablename GROUP BY streetid Where this might get more GIS-y and interesting is the case where streets with the same ID are not contiguous. You didn't mention if this is a ...


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

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


6

The problem is that you've got the wrong coordinate order. WKT for SRID 4326 is longitude then latitude (think Cartesian, it is X then Y). The error is telling you that -122 is not a valid latitude. The geometry (or geography) needs to look like: SELECT ST_GeographyFromText('SRID=4326;POINT(-122 37)')... SELECT ST_GeographyFromText('SRID=4326;POINT(2 ...


6

Although someone else with more experience than I may be able to answer better, this is my understanding: To edit data in a spatial database, you need ArcGIS Server (the SDE components) That database can be anything, PostGIS, Oracle or SQL Server You can use native geometry types in the database, which should mean you can edit/view with other software. ...


6

The table gt_pk_metadata is an optional table that GeoTools (and GeoServer) use to work out what the primary key columns in a view are. It is needed to generate consistent feature IDs (FIDS) otherwise GeoTools will use the feature's java ID which will change from run to run. It is explained in this document. So you can ignore this error if you don't care ...


6

If you are working on your workstation it's more a matter of taste. Knowing how to use psql is useful for some situations like running sql scripts from files, pipe it with other tools, etc. It depends on your needs. My everyday work is done using pgAdmin and I only go down to the CLI when needed. On the other hand psql is sometimes your only option when ...


6

The pgShapeloader tool has the ability to upload a shapefile in a different schema than public. Just double click the public word and type your schema name instead:


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


5

Clip and Intersect are two similar, but different operations, but that's not your problem here. It looks to me like your Intersect worked fine, the problem is that the shape you're Intersecting with, the red frame, is a line object and not a polygon. So, the only road segments being output are the ones that directly touch that line. If you want to clip the ...


5

When you spatially-enable a PostGIS database, the relevant functions, SRS table, and views are placed in the public schema, as you state. That does not mean that all or any of your own spatial tables need to be in the same public schema. PostGIS will still work on all spatial data in the "new" schemas. In fact, I usually place my application-specific tables ...


5

You no longer need to use the templates or even the script in the new versions of Postgis. As you can see in the Postgis - installation page, all you need is run the CREATE EXTENTION command. -- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION ...


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

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


4

The error you are seeing is because ST_MakeLine does not accept WKT style arguments. There are multiple usage options, but for you, probably something like: ST_MakeLine(ST_MakePoint(700264, 126376, 23700), ST_MakePoint(780844, 122481, 23700)) will be best. That may not fix the real problem (in that you aren't specifying the table that County multipolygon ...


4

For convert "Multipoint" to "Point", you have to use ST_Dump, for example: SELECT (ST_Dump(the_geom)).geom AS the_POINT_geom FROM MULTIPOINT_table; On the question about use "Multi" o single geometries, I use this logic: if every geometry has different attributes -> Single if a group of geometries have same attributes -> "Multi"


4

Yes, you can render your data using a middleware like GeoServer. Regarding your icon set request, first result doing a simple Google search http://mapicons.nicolasmollet.com/ Have you done any research at all? This other from MapBox is nice also.


4

I've used 2 approaches to this problem: A table with meta data and a geometry column containing a MultiLineString. A table with track meta data and a numeric primary key, and a second table with a geometry column containing Points, and auto incrementing primary key for ordering, and a relationship to the track table. Pluses to #1: You can run all of ...



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