Tag Info

New answers tagged

0

Changing the CRS property does NOT change the coordinates. Inside QGIS, you have to use Save As ... to another file name and CRS EPSG:4326. Then the coordinates will be inside the bounds of +/- 180/90°.


0

Use ST_Line_Interpolate_Point union them first to create a single complete without gaps line .


0

This isn't exactly what your looking for, but if your comfortable with python i've got a script that takes a gpx file and exif tags photos with location, including interpolating between points, and it could easily be modified to read the GPS data from a format other than gpx. https://gist.github.com/JesseCrocker/9c4b8e334ceba63f3d08


1

CREATE VIEW results AS SELECT somefield, (ST_Dump(ST_Union(geom)).geom geom FROM somelayer GROUP BY somefield;


2

Go with steps: -Create spatial indexes for your tables. -Create Buffers CREATE OR REPLACE VIEW b1 as SELECT ST_Buffer(the_geom,500) as the_geom, attributes, fid FROM stores; CREATE OR REPLACE VIEW b2 as SELECT ST_Buffer(the_geom,2000) as the_geom, attributes, fid FROM stores; (I am not sure, but maybe you should reduce here you 8 million point to ...


1

Your query looks rather strange. What are you trying to achieve with two buffers around the same point? You can replace ST_Distance with operator, but still we have to overthink the buffer thing. Could you specify your goal a little? PS: Did you build spatial index? UPDATE: I would try SELECT * FROM ( SELECT a.gid, b.gid, ...


0

I am thinking about the same questions at the moment. We are looking into securing a WFS-T for a government contract. Depending on your setup you may send the request from your client to a custom service using XACML which checks the current user and then either rejects or forwards the AJAX call to Geoserver. There is probably a lot of work involved to get ...


1

The query you're using is correct, but a detail for you to take into account. ST_Area result will be expressed in SRID units if you use directly the_geom. If you want to get the area in square meters, you need to cast your geometry to geography, by using: UPDATE cartodb_query SET area = ST_Area(the_geom::geography)


1

see my solution: first you need to create line from your points: ST_MakeLine(geometry geom1, geometry geom2); http://postgis.refractions.net/docs/ST_MakeLine.html And than you need to create a buffer around the line, but because you are in degree coordinate system (probably wgs84) you should transform the line into another coordinate system (meter based). ...


0

She will need the GDAL package, which comes with the gdal_polygonize.py script when python support is enabled at compile time. It sounds like she already has the python bindings installed, so she likely already has GDAL. If she can locate the GDAL install directory, try looking under its /bin subdirectory--it should be there. If she finds it, just add ...


0

Do your various tables in you DB have spatial indexing?? for more info go to what is spatial indexing That could be a solution


1

Use the &&& operator for a hypercube query. Since like its brother && it is a bounding box query, you just need to create a query geometry that spans the desired cube: a linestring is the easiest to make: SELECT * FROM myTable WHERE point &&& 'LINESTRING(50 50 50 50, 100 100 100 100)';


0

The psql option for executing sql commands is -c and not -e. The -e option means (from the psql help): -e, --echo-queries echo commands sent to server Because the -e option does not take arguments, your sql command is simply ignored. What you need is: -c, --command=COMMAND run only single command (SQL or internal) and exit Also,this ...


0

This is a much simpler method.. UPDATE lines SET GEOM = ST_LineMerge(ST_Difference(GEOM,(SELECT ST_Union(ST_Boundary(GEOM)) FROM polys WHERE ST_Intersects(GEOM,lines.GEOM) = 1))) WHERE (SELECT geometry FROM polys WHERE ...


5

Yesterday I had no time to create it in details... See my solution in 4 steps: CREATE OR REPLACE VIEW bd_segment AS SELECT ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) AS sp, ST_PointN(geom, generate_series(2, ST_NPoints(geom) )) AS ep FROM -- extract the individual linestrings (SELECT ...


2

Because you are using the geography type instead of the geometry type, the corners of your rectangle are connected by great circles, not lines along the parallels as you might be expecting. Your query works just fine if you cast your rectangle and point to ::geometry. SELECT ST_Covers(geog_poly::geometry, geog_pt::geometry) As ...


1

This behavior I've seen before with point data and have not figured out a workaround for the existing layer. The workaround that I've used in the past is to make a copy of the table in the database, call it something different, and re-create the layer. When you are at the Bounding Box section, make sure to over estimate the bounding box values slightly (or ...


1

The most natural way to do this is with a cross join, using ST_Contains in the where clause to restrict the result. ST_DWithin checks for things within a certain distance of any geometry, not for containment, as you need. CREATE TABLE grid_tweets AS SELECT pts.*, grid.gid as gridID FROM "tweets" AS pts, "grid_jakarta" grid WHERE ST_Contains( ...


0

You must enclose your table name in double quotes for PostgreSQL to recognize capital letters. --> "Seg_060_2009"


3

Maybe I'm missing something, but why do you need a function for this? And why the loop? UPDATE own_locations SET ford='t' WHERE cars LIKE '%Ford%';


0

Take a look at the answer to this question. It's not a tutorial, but it might be enough in your case: http://gis.stackexchange.com/a/116444/800


1

Rather than leaping direct to WPS you might find you can get what you need out of the GeoServer SQL View feature. http://boundlessgeo.com/2015/03/support-story-getting-sql-views/ http://docs.geoserver.org/stable/en/user/data/database/sqlview.html It allows you to write some arbitrary SQL and bind some query parameters into it, so the query becomes a ...


2

You could try using geoalchemy2. Personally I would look at using the Object Relational Mapper (ORM) model for working with your data, for example: from sqlalchemy import create_engine # Enter your database connection below engine = create_engine('postgresql://gis:gis@localhost/gis', echo=True) from sqlalchemy.ext.declarative import declarative_base from ...


0

A slighty more exteme version than that posted by @Guy, would be the following. 1). Break the Polygons into constituent linestring. You can do this using ST_Dump and two sets of generate series offset by one, to get each pair of adjacent points, which is then handed to ST_MakeLine. 2). Get a list of distinct linestrings. ie, removing duplicates, as this ...


0

Assuming they exactly align, then you can load in your polygons into a PostGIS table a and your lines into table b and use the following in SQL to return confirmation SELECT ST_Covers(ST_Collect(b.line), ST_Collect(ST_ExteriorRing(a.polygon)) FROM a,b (untested) There are plenty of ways you could achieve the same result, but it really depends on the size ...


0

Sorry I know my response is a little late. I faced a similar problem inserting Point coordinates to postgis but was successful when I replaced the comma in the coordinates with a space; [-48.23456,20.12345] to [-48.23456 20.12345] INSERT INTO layer_radar (id, geom) VALUES ( '3bf24920-225b-11e4-8c21-0800200c9a66', ...


1

Any valid URI should be working, if it's not, it might be a client issue. I know we do a lot of WFS-T with URIs setup as <company's site>/<projectName> (e.g, http://my.company.com/myproject) from OpenLayers with no issues, but don't have direct experience doing it with QGis.


1

The Boundless OpenGeo Suite packages should work alongside homebrew as long as you either don’t run them at the same time or change the port on one of them.


0

You cannot have WFS for Grouped Layers. Grouped Layers are an Geoserver specific service, which works as a Single WMS service containing multiple Layers. This is outside the scope of OGC standards, and it only returns images. Having a vector group layer for multiple layers does not make sense. You should be able to access the individual layers as WFS.


1

You want something more like insert into prods (column_name, ...) values (?, ...) Databases generally don't let you use placeholders for identifiers (table names, column names, ...). PostgreSQL is complaining because you're trying to use the first three placeholders as column names: INSERT INTO users($1, $2, $3) ... You can think of identifiers like ...


0

A little help: Create buffer views: b1 (10000), b2 (50000), b3(100000) example: CREATE OR REPLACE VIEW b1 as SELECT ST_Buffer(the_geom,10000) as geom, store_name, fid FROM stores; Create ring views: r2, r3 example: CREATE OR REPLACE VIEW r2 as SELECT ff.fid, ff.store_name, ST_Difference(ff.the_geom, g.the_geom) as new_geom FROM b2 ff JOIN b1 g ON ...


0

Here is a pseudo SQL. Unfortunately SQL fiddle doesn't support PostGIS. create table customer ( loc real, name text ); create index on customer(loc); create table location ( loc real, name text ); create index on location(loc); insert into customer values (1,'c1'), (2,'c2'); insert into location values (1.1, 'l1'), (1.2, 'l2'); select cat, count(*) ...


4

There is simple query for this case. SELECT a.id AS store,count(b.*) AS customer_count FROM stores a, customers b WHERE ST_DWithin(a.geom::geography,b.geom::geography,5000) GROUP BY a.id I have stored my sample data in the projection WGS 84 (4326). When you want to use a metric system, you have to convert the geometries to geography format. The ...


2

The question is a duplicate of the one here but it may not be so obvious so let me explain. ST_Within is defined as: -- Inlines index magic CREATE OR REPLACE FUNCTION ST_Within(geom1 geometry, geom2 geometry) RETURNS boolean AS 'SELECT $1 && $2 AND _ST_Contains($2,$1)' LANGUAGE 'sql' IMMUTABLE; As you can see the function does use ...


1

I'm the author of this particular code, but I'm not sure what could cause this. Do you have the latest Tilemill version? Maybe you could try using an older openstreetmap-carto version, for example 2.28.0: https://github.com/gravitystorm/openstreetmap-carto/releases/tag/v2.28.0 Also, does duplicating the code, like this, help by any chance? #text-poly { ...


0

I suppose that this problem occurs only when the layers are inserted through "Add vector layer->Database->PostgreSQL". In this case QGIS uses the OGR provider to communicate with PostgreSQL database. There seems to be a bug in the implementation of the OGR provider in QGIS>1.8 causing reopenings of the dataset for every record and every table (thus thousands ...


0

To my amateur eye, you need to cast the PDAL patches as geometries and union the result. ST_Union()or ST_MemUnion() for memory efficieny. Apologies for lack of testing - wrong computer. SELECT st_union(pa::geometry) FROM medford;


2

Shouldn't ST_GeometryFromText(Point(53.34972 -6.26025)), 4326) have quotes around the WKT since the function expects a text argument? So this instead - ST_GeometryFromText('Point(53.34972 -6.26025)', 4326) which also removes the extra right parentheses that you had after the WKT.


1

I would suggest another approach. If you do not need vector data only OSM as a base map which looks the same as http://www.openstreetmap.org maybe you could create your own OSM service using Mapproxy. I know it is not perfect, but maybe you could give it a try. First you need to install Mapproxy. Pre-seed all zoom levels of OSM for particular area and then ...


1

Thusly: SELECT Max(id) AS id, array_to_string(array_agg(name), ',') AS all_name, geom FROM thetable GROUP BY geom; The only fiddly bit is there's no nice way to get the geometry and id group the grouping together cleanly, so ended up only grouping on geom. If there was a "max(geometry)" aggregate or something it would be easier to group by "id" ...


2

What you are looking for is essentially an upsert (albeit without the update part). This is not part of ogr2ogr and is fairly complicated to implement in Postgres, see the docs, for more information than you would ever want to know about upserts. A simple alternative, would be to use a temp table to insert into from ogr2ogr and then run an insert for only ...


4

If you haven't already add a geometry column (assuming EPSG:4326 here): SELECT AddGeometryColumn ('your_table','geom',4326,'POINT',2); Then UPDATE, passing in the names of your x and y fields: UPDATE yourtable SET geom = ST_MakePoint(longitude, latitude); This will update the entire table, unless you include a WHERE clause


5

If you want to use a postgis database with Openstreetmap data, you have to populate it with osm2pgsql, not osmosis. That is the only way to get polygons out of Openstreetmap multipolygon relations and closed ways in a format that GIS software can understand. Alternatively, you can use GDAL ogr2ogr to save OSM data into a spatialite database. Once you have ...


0

use QGIS Open the map file by ََQGIS Select the items map by type style the items one by one see the tutorial here


4

ST_Polygonize will do the job: CREATE VIEW boundarypolygons AS SELECT g.path[1] as gid, g.geom::geometry(polygon, 31492) as geom FROM (SELECT (ST_Dump(ST_Polygonize(geom))).* FROM boundary ) as g;


1

Since you are using PostGIS and QGIS, you can try to convert them using both, to decide which one is best suited for your problem. To use PostGIS, the LINESTRING must be closed. You can check if they are closed with the query: select gid, st_isclosed(geom) from boundary; If the lines are closed, you can create another table to check the results, with: ...


1

To load a shapefile into QGIS and set the layer as "editable", I executed the code below in the Python Concole: LayerName =iface.addVectorLayer("/Path/To/Your/Shapefile.shp", "name", "ogr") qgis.utils.iface.setActiveLayer(LayerName) qgis.utils.iface.actionToggleEditing().trigger() For PostGIS tables this blog post may help to set up the SELECT statement ...


1

The following program ArcMap2SLDConverter will generate an SLD file from an ArcGIS Map - but you must have ArcGIS installed and running to do so. This SLD can then be uploaded to GeoServer, and used to style the corresponding layer.


0

My current workaround is to build a side table with 2 columns, the objectid and geometry( as PostGIS geometry). And to maintain that table with triggers on the featureclass. Then perform spatial queries against the side table and join back to the featureclass. I have an open incident with Esri, but so far, no luck. I will update this answer if/when we make ...


2

Might want to try HausdorffDistance. Suited for this kind of question. SELECT ST_HausdorffDistance('0101000020110F0000FE546AD2413E5EC16D56617C93C74FC1'::geometry, '0101000020110F00009D7E6384413E5EC10185DD7F93C74FC1'::geometry); Detailed here: http://postgis.net/docs/ST_HausdorffDistance.html And as explained at /wiki/Hausdorff_distance: Informally, ...



Top 50 recent answers are included