Tag Info

Hot answers tagged

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


3

The call to SelectLayerByAttribute could be handled like this: ARCPY.SelectLayerByAttribute_management('cities', "NEW_SELECTION", "CITY_NAME = '{}'".format(city))


2

Schemy just build the build the bounding box check in your query or wrap the pgr_dikstra in a function that does e.g. SELECT SUM( (SELECT ST_length(ST_Transform(the_geom,28355)) FROM ways w WHERE w.gid = id2 )) as distance_in_metres FROM pgr_dijkstra( 'SELECT gid AS id, source, target, ...


2

I'm assuming that you are using Geometries, but the methodologies remain mostly the same. When tuning spatial queries, these are the steps I take This is the most important step. Check the indexes are suitable for the table. If you have SQL Server 2012+ then I would suggest that you use the AUTO GRID. This gives you a finer grid. Make sure the extents ...


2

Unfortunately with SQL Server 2008 R2 your options are quite limited. If you are able to enable CLR on your server, you can use something like this to add extra capabilities, including aggregates. Another option would be to create a procedure/function using CURSORs to step through the dataset and do the Unioning. This would likely to be quite slow. ...


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

Normally, no, you cannot just change the SRID value. SRID stands for Spatial Reference System Identifier and is a coded value describing what coordinate system the data's geometry is in. Let's take the example of the two CRS (coordinate reference systems) you have. 4326 is WGS84, which is a geographic CRS with degrees for units and the WGS84 datum. 27700 is ...


1

I use the GEOMETRY::UnionAggregate function in SQL Server to do this for our Elem, Mid and High boundaries In the dbo.SchoolBoundaries table below, the ELEM_NUM value is what I want to group on, and values of 0 are non-district boundaries (which I filter out): SELECT elem_num , GEOMETRY::UnionAggregate(shape) as shape FROM [dbo].[SchoolBoundaries] ...


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


1

No solution, but a workaround: in SQLExecutor transformer select Microsoft Access (MDB_ADO) as the format, edit the SQL string, press ok, save, execute notepad++, open fmw-file, search&replace MDB_ADO with XLS_ADO, add some additional lines (XLS_ADO_SHOW_NAMED_RANGES, XLS_ADO_FIRST_ROW_IS_HEADING), save. Start working. Obviously FME2014+ recognizes the ...


1

What I have found speeds these intersection queries up the most is forcing the spatial index: In a view I deliver to BI which intersects ~280,000 address points with ~300 boundary polygons, I force the use of the address point spatial index: ... FROM [dpsdata].[Address_Master] as am with (index(SIndx_AddrMsterIC)) left outer join ...



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