Tag Info

New answers tagged

1

This question is more than a little confusing ("point data is sample not really very" what?). Your SQL query syntax is highly irregular, and fails to use the correct function to take advantage of a spatial index on the point layer. What you probably want is: SELECT b.shape FROM table_b b, table_a a WHERE ...


2

Use UNION or UNION ALL to build one result from your two selects: insert into table_1 (name, id) select name, id from table_2 limit 1 UNION select name, id from table_3 limit 1; UNION will remove the duplicated rows from the union, UNION ALL will include all.


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


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


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


0

You need the mapcatalog to store spatial data in the database from MapInfo Pro and for MapInfo Pro to be able to read the spatial data from the database. If you don't have the mapcatalog and your table is spatial/mappable in MapInfo Pro chances are that you aren't reading/writing the spatial data to the database but just storing it in a local .map file ...


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

Well PostgreSQL 9.2.2 with PostGIS 2.0.x is the highest version supported officially in MapInfo v12.5, and you are using newer versions! MapInfo v12.5 - Install guide


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


0

Here is a basic trigger that will get fired when a record gets updated: CREATE TRIGGER dbo.trg_MY_TABLE_OnUpdate ON dbo.MY_TABLE AFTER UPDATE AS Begin Update dbo.MY_TABLE Set DATE_CHANGED = CURRENT_TIMESTAMP, USER_CHANGED = SYSTEM_USER Where MI_PRINX In (Select MI_PRINX From inserted); End GO and a similar one for a record ...


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


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


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


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


0

I think you should use arcpy.GetParameterAsText(0) to pull in the city name and guarantee that it is a string. You can then print it to confirm its name before using it in SelectLayerByAttribute. Changing: ARCPY.SelectLayerByAttribute_management("cities", "NEW_SELECTION",''' "CITY_NAME" = ' city ' ''') to: ...


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



Top 50 recent answers are included