Tag Info

New answers tagged

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


1

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


1

That data appears to be in the following coordinate system based on the files I found here. You need to reproject/transform all of your data to be in the same coordinate system, whether it be on the fly in GIS software, or permanently in the data itself. Clarke_1866_Albers Authority: Custom Projection: Albers, False_Easting: 0.0, ...


3

There are various forms of ST_Value and the one that will probably help you is the ST_Value(rast, band, x, y) one. Modifying one of the examples from the docs, you can dump all the values for each pixel using generate_series in the x and y direction, and then use group by (or distinct) on those to get the list of unique pixel values. SELECT count(b1val), ...


0

Many thanks for the quick reply - I have however been able to reconcile the distance values between the two methods (ArcMap/SQL spatial tool/Cross Apply). I apologize for the distraction and appreciate the code and link. I know I am squarely on my way.


1

Here's a link to MSDN on Nearest Neighbour queries. Luckily you are using 2012 so the indexes will be used. A query like his should be close to what you want. SELECT pl.ID polyID, pt.ID pointID, pt.Distance FROM Polygon pl CROSS APPLY ( SELECT TOP 1 p.ID, p.SHAPE.STDistance(pl.SHAPE) Distance FROM Point p WHERE p.SHAPE is not ...


1

Take a look at ST_ShortestLine. http://postgis.net/docs/manual-2.1/ST_ShortestLine.html ST_ClosestPoint is the first point in ST_ShortestLine. Something like this should work. SELECT DISTINCT ON (a.id) ST_ShortestLine(a.geom,b.geom) FROM table1 a,table2 b ORDER BY a.id,ST_Distance(a.geom,b.geom);


3

The IN operator is what you're looking for. Use a comma-separated list in parentheses, like so: "OGF_ID" NOT IN (214620160, 214620161, 214620162)


2

To complete these type of relational database queries using an ArcGIS geodatabase (not a relational database as mentioned above) you will have to use a tool like Summary Statistics To do the groupings, use the 'case' field (ie. the field you want to group by) The SUM option Adds the total value for the specified field.


2

Assuming that id is the primary key for your points table, and that you want to group point ids which are 10km apart in order to select a best point from each collection. Note that your distance parameter in this case should be the assumed diameter of your city, not the radius. This code will first, for every point, create an ordered array of points within ...


2

I think your p1.id=p2.id is the problem. If you change it to p1.id<>p2.id and add a p1.name='Eneby, I think you'll get what you are after. with cities as ( select 1 ID, 'Eneby' as CityName, ST_MakePoint(10, 10) geom union select 2 ID, 'Eneby' as CityName, ST_MakePoint(11, 11) geom union select 3 ID, 'Eneby' as CityName, ST_MakePoint(11, 12) geom ...


2

It might be a solution : 1 create buffers radius 5 for each point, keep attributes ; 2 select buffers intersecting each other with same name ; 3 keep the "best" cities ; What do you think ?


1

Building off the other answer, you could aggregate the answers into an array, and then use the PostgreSQL random function multiplied by the array_length to get a random index for that array. The + 1 at the end of that is because array indexes start at 1 in PostgreSQL WITH arrayed_data AS(SELECT ARRAY_AGG(a.tile, ',' ORDER BY a.tile) AS tiles FROM ...


1

SELECT g.id , SUM(o.netdwellings) as sum from g , o WHERE g.geom.STIntersects(o.geom) = 1 group by g.id That would return sum of all o.geoms that intersect with g.geom. (did i understood question wrong ?)


1

What attributes you have and how you name them should depend mostly on the application domain or themes within your GIS the detailed requirements of your application your own organization's conventions for normalising database tables your own organization's conventions for naming database attributes If it is a topographic database, study the attributes ...


2

Just because Oracle supports the creation of custom objects in the database doesn't mean that all clients will be able to read their values. In this case, your PyODBC client doesn't know how to unpack the SDE.ST_GEOMETRY column type, so it hasn't told Oracle how it will handle geometry, and Oracle is generating the error saying it hasn't been told how to ...


-1

I have now also tried the following statement, which verifies OK in ArcGIS. It removes the last IN statement, replacing by OR. This again gives me 3051 records... (railway NOT IN (Null,'') AND osmSupportingElement = 'no') AND NOT ((railway IN ('platform') AND (osm_level LIKE ('%-%') OR osm_layer LIKE ('%-%') OR osm_location = 'underground')) OR (railway IN ...


1

The reason they don't add up is that in query 1, you have a condition Query 1: (this) AND NOT (that). Queries 2 and three have only one condition Query 2: (this) Query 3: (that) You undoubtedly have some records where (this) is true AND (that) is true and also where they are both false. Try modifying query 1 (take out NOT) (railway NOT IN (Null,'') AND ...


0

I have found some solution to do this with one query (Oracle 11.2): WITH recursive (p1, p2) AS ( SELECT thin.connection_id, thin.geom FROM thin_lines thin, thick_lines thick WHERE sdo_touch(thin.geom, thick.geom) = 'TRUE' AND thick.color = 'green' AND thin.color = 'blue' UNION ALL SELECT thin.connection_id, thin.geom FROM thin_lines thin, ...


2

There is a differences between ArcSDE and File GDB SQL query syntax for Date fields: SQL reference for query expressions used in ArcGIS (10.2)(Dates and time section) http://resources.arcgis.com/en/help/main/10.2/index.html#//00s500000033000000 It appears you need to encapsulate your column names in double quotes and precede your values with date. I ...


2

I haven't got a installation of postgis to test this on, but something like the following should work. SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY code ORDER BY ST_Distance(geom, ST_SetSRID(ST_MakePoint(-90,40),4326)) RN FROM Table ) Closest WHERE RN = 1;


2

The postgres provider requires integer datatypes (i.e. ones, that are internally handled as QVariant::Integer or QVariant::LongLong) Integer: int2 int4 LongLong: int8



Top 50 recent answers are included