Tag Info

New answers tagged

0

You can only write such SQL if you use Personal Geodatabase (ACCESS) and if you open the geodatabase in Access. In this case you can use the IIF function as follows: iif ( condition, value_if_true, value_if_false ) Here is an example. You can't use full featured SQL using Personal Geodatabases in ArcGIS Desktop, only limited SQL-like expressions to ...


2

I suggest this: SELECT TOP 3 * FROM MyTable WHERE NOT [Field2] = '%Variable%' ORDER BY [Field1] Example Let's start with an unordered table: +------------------+------------+ | rainbow_sequence | color_name | +------------------+------------+ | 2 | orange | | 3 | yellow | | 1 | red | | ...


1

All four of the databases you listed have the capability to support the type of query you're asking about. SQLite and Postgres through extensions (Spatialite & PostGIS respectively), mySQL and MariaDB have some basic GIS support built in natively. As to which to use, it depends on your needs. Personally I go with SQLite for simple in house ...


1

SELECT ST_X(pts.geom) as lon, ST_Y(pts.geom) as lat, poly.attribute FROM points pts, polygons poly WHERE ST_Intersects(poly.geom, pts.geom); where it is assumed that the attribute you want from the polgons table is called attribute. For non-trivial table sizes, you will want a spatial index on the geometry columns of polygons/points tables.


1

Select points.*, polygons.* from points inner join polygons on st_intersects(points.geom,polygons.geom);


10

'foo' is the name that has been assigned to the sub-selection. It has no function other than being syntactically necessary - a sub-selection in PostgreSQL must have a name assigned to it, but it does not matter what the name is. 'foo' was likely chosen because it is a common meaningless placeholder word used by programmers, similar to 'lorem ipsum' used by ...


2

Yes, you will need to use the external procedure agent if you want to access the XML columns in the SDE.GDB_ITEMS_VW views. By default, the XML columns in the GDB_* tables are stored using ESRI's XML implementation. The functionality to convert these to a CLOB is included in the ST_Geometry library, which is accessed using the external procedure agent. ...


3

Your Line ... WHERE ST_geometrytype(geom) = ST_Point; Indicates that ST_Point is a column since it is not in quotes. I see here also a comment from @Vince that is saying basically the same thing that I am. Since ST_Point is not quoted in your expression is is trying to evaluate what is in ST_geometrytype(geom) against another field/column and it can't ...


0

I'd try SELECT DISTINCT --MY FIELDS-- FROM Points WHERE EXISTS (SELECT 1 FROM Polygons1 WHERE Polygons1.Shape.STIntersects(Points.Shape) = 1) AND NOT EXISTS (SELECT 1 FROM Polygons2 WHERE Polygons2.Shape.STIntersects(Points.Shape) = 1)


0

Try intersecting the two polygons first using STIntersection as a sub-query, then run an STIntersects on the points against the Intersection of the two polygons. Something like this: select points.geom , points.OtherFields from ( select p1.geom.STIntersection(p2.geom) as geom from dbo.polygonLayer1 as p1 , ...


0

The solution in PostGIS would be something like below that might lead you in the right direction also in SQL Server. I don't know if SQL Server have ST_Difference http://www.postgis.org/docs/ST_Difference.html SELECT point FROM pointTable INNER JOIN (SELECT COALESCE(ST_Difference(a.polygon,b.polygon),a.polygon) diffPolygon FROM polygonTableA as a LEFT ...


1

When you apply an SELECT SQL query from the CartoDB editor, a green bar appears as a header with two options: create table from query and clear view. Moreover, your table view gets updated with the contents that fulfill the applied SQL query. However, when you expand this query by adding some additional filtering parameter (or replace the applied query ...


2

This reminds the old quiz: 5 person will shake hands with each other. How many handshakes will there be in total? Is it 20 because everybody must shake hands with 4 other person? No, because if Mike shakes hands with John, John does not need to shake hands with Mike for another time. Let's assume that you have 5 points. Point number 1 does not shake hands ...


0

You can do that summation in the SQL statement behind the table in CartoDB. When you are in the Data View for the table, click on the SQL sidebar and you will see something like: SELECT * FROM crocs_table To include your summation, you would modify that statement to: SELECT *, (num_croc_male+num_croc_female) as total_crocs FROM crocs_table You need to ...


1

There are a couple of options. Option 1 You could do the query exactly like you have it, then add your linestring using GeoJSON on the client. You'd use CartoDB.js to draw you polygon layer and then use Leaflet to add the GeoJSON layer on top. Option 2 Use a multilayer visualization. The bottom layer you could have as you polygons (styled accordingly. ...


1

I tried your commands and changed filename raster, to filename char(250), , assuming you meant to use a string for that (and declaring it as a raster is a typo??). The commands seem to work without -C. With -C, I got some warnings/notices about numeric field overflow and no_data. But I guess that's just something in my data. What I tried is: echo ...


0

Look into this. I think this will solve your problem. http://arcscripts.esri.com/details.asp?dbid=15501


0

The Microsoft Developer Network help docs seem promising. Here is an example from the help on to test for intersection: DECLARE @geom1 geometry; DECLARE @geom2 geometry; DECLARE @result geometry; SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1; SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2; SELECT @result = @geom1.STIntersection(@geom2); ...


1

In OGR, the elevation data gets stored in a point layer called track_points. You can do "normal" ogr2ogr operations preserving the elevation data like this: ogr2ogr -f GPX output.gpx input.gpx waypoints routes track_points However, that fails on your ST_UNION command because you requested a Multilinestring (which has no vertex elevation data by ...


1

If you are into Java and want Flexibility, Graphhopper is an excellent alternative. Peter, the project lead, actively encourages extensions and changes and the community is very responsive. Graphhopper works differently to Pgrouting (I use both) and it is extremely fast. Instead of using a PostGis database, Graphhopper builds its graphs direct form the .pbf ...


1

If you had the 3 different columns in your CartoDB, you could update it with the SQL API. So if you wanted your CartoDB table (we'll call it crocs_table) looked like this: num_croc_male num_croc_female total_crocs Row1: 5 10 15 You could update it it using the SQL API like so: ...


1

Open your GeoServer Admin Instance at http://yourhost:8080/geoserver/web/ and log in .. To use SQLViews, you must have a database configured and available with data in it .. usually PostGreSql, and you must have already configured a Database DataStore to use SQL Views. Select layers -> Add new Resource (green + button at the top) In the drop down list of ...


2

You need to cast from the output of ST_MAKE() which is a geometry to the geography data type. This casting is done by using the :: operator. The following command should work: UPDATE customer_campaign set geom=ST_MakePoint(customer_campaign.long, customer_campaign.lat)::geography;


0

The Topology Checker plugin is a good tool if used correctly. You still have to have a fundamental understanding of your data AND you have to make the 'corrections' manually. The plugin will highlight what it thinks are errors. It is up to you to then examine each and make the appropriate decision for you and your data. With 90 000 items in your layer, you ...


2

The following query appears to do a reasonable set of voronoi polygons starting from the Delaunay Triangles. I'm not a big Postgres user, so it can probably be improved quite a bit. WITH -- Sample set of points to work with Sample AS (SELECT ST_GeomFromText('MULTIPOINT (12 5, 5 7, 2 5, 19 6, 19 13, 15 18, 10 20, 4 18, 0 13, 0 6, 4 1, 10 0, 15 1, ...


1

You can do this in SQL using a spatial self join. You don't state which SQL dialect you are using, so this example uses Postgres/Postgis, but it could be easily adapted to Oracle or SQL Server. Assuming a table called buildings, with geometry stored in a column called geom: SELECT a.id, b.id from buildings a, buildings b WHERE ST_INTERSECTS(a.geom, ...


3

I would use Python's itertools and a SearchCursor for a very efficient way to find the spatial relationships you are after. You can incorporate the geometry methods overlaps, contains, and equal to get at the geometry properties. Start off by creating a function to better organize the workflow and for repeatability def findOverlaps(x): Open a search ...


0

in QGIS, Topology Checker plugin can propably solve your problem


2

I have an idea what may work for you. It is going to be based off some assumptions, but it would help narrow down your list of possible identical features. This would not be an automated process, but it would require manually looking at the duplicates. Based off the comments, it seems like the automated tools don't compare attributes so this would help ...


0

I would use the Select tool and then write this expression: "Modified_date" >= CURRENT_DATE -1 It should return all your records greather than equal today minus 1 day (i.e. 24 hous), therefore yesterday at 6am. It definitely works with the Definition Query SQL as we use it in our organisation to only view our "incidents" in the last fortnight: ...


3

There is an easier and more efficient way of doing this. Works for both PostGIS 2.1 and 2.0. Just use the ST_ValueCount function. http://postgis.net/docs/manual-2.1/RT_ST_ValueCount.html that will give you both the pixel value and number of pixels that have that value. So would be for your case SELECT DISTINCT (pvc).value FROM (SELECT ...



Top 50 recent answers are included