GIS SQL Query Logic Syntax

Question

I have a GIS layer that has thousands of Facility IDs.

For each Facility ID there can be multiple Chemical ID's.
For each chemical ID there is a maximum of 3 zone types. Zone A, B, C. But there is not always 3 Zone types, it varies.
And the amount of chemical ID's for each Facility ID can vary from 1 to many.

What I am attempting to do, is Dissolve (or group by) this layer down to display which Facility ID Zone has the largest Buffer Distance while including the ZoneType(important)

Here are the fields

             FacilityID, ChemicalID, ZoneType, ZoneDistance
ex rows:        1              2           A          1000
                1              2           B          900
                1              2           C          500
                1              5           A          1200
                1              5           B          900
                1              7           B          2000
                1              7           C          900
                2              13          A          200
                2              13          B          300
                2              13          C          600

expected result: 1 row for every FacilityID with the Max Buffer and that specific zone type. So for FacilityID 1- I want one row and it would be ZoneType B with a ZoneDistance of 2000

FacilityID,ZoneType, ZoneDistance  
    1          B         2000
    2          C         600

I have tried a few SQL statement which I got the Facility ID with the Max ZoneDistance for each ZoneType. I just want the Max ZoneDistance for all the ZoneTypes at a FacilityID.

SELECT max(ZoneDistance), ZoneType, FacilityID
FROM AllZones group by ZoneType, FacilityID;

I have also tried a Subquery but that did not work either

I am semi new to SQL, and I cannot seem to figure out the logic to get my results. Answers welcomed in SQL or Python

what gis function are you using? Sql Server, Postgres, Oracle? — Juan Carlos Oropeza, Jun 03 '16 at 16:38
`Max(ZoneDistance)` Mean buffer area covering all `ZoneType` from each facility?? — Juan Carlos Oropeza, Jun 03 '16 at 16:41
it could be relevant if there is a way to use a GIS tool in either ArcGIS or QGIS — ziggy, Jun 03 '16 at 16:44
a GIS Layer- think of it as a database table with columns, rows with spatial geometry so it can be displayed in mapping software — ziggy, Jun 03 '16 at 16:48
You really should consider using Postgres instead of Access. I work with ArcGis and Qgis and is very good. And make this query would be much easier. Just a `MAX() over (PARTITION BY ZoneType, Facility)` — Juan Carlos Oropeza, Jun 03 '16 at 16:50
Yeah I know Postgres is great but I work for the state and they do not let me download Postgres :( thanks Il give your query a try — ziggy, Jun 03 '16 at 16:52
1 row for every FacilityID with the Max Buffer and that specific zone type. So for FacilityID 1- I want one row and it would be ZoneType B with a ZoneDistance of 2000.. — ziggy, Jun 03 '16 at 17:01
Please update your question and include the exepcted output. The same format you include your sample data. — Juan Carlos Oropeza, Jun 03 '16 at 17:06

score 0 · Answer 1 · edited May 23 '17 at 12:08

Using this example in access. I prepare a demo on MySQL.

Achieving ROW_NUMBER / PARTITION BY in MS Access

SQL Fiddle Demo

SELECT *
FROM (
        SELECT 
            t1.`ZoneType`,
            t1.`FacilityID`,
            t1.`ZoneDistance`,
            COUNT(*) AS `rn`
        FROM       Facility AS t1
        INNER JOIN Facility AS t2
                ON t1.`FacilityID`   = t2.`FacilityID`
               AND t1.`ZoneType`     = t2.`ZoneType`
               AND t1.`ChemicalID`   = t2.`ChemicalID`
               AND t1.`ZoneDistance` <= t2.`ZoneDistance`
        GROUP BY
            t1.`ZoneType`,
            t1.`FacilityID`,
            t1.`ZoneDistance`
        ORDER BY 
            t1.`ZoneType`,
            t1.`FacilityID`,
            t1.`ZoneDistance` DESC
       ) T
WHERE rn = 1      
ORDER BY `ZoneType`,`FacilityID`

OUTPUT

| ZoneType | FacilityID | ZoneDistance | rn |
|----------|------------|--------------|----|
|        A |          1 |         1200 |  1 |
|        A |          2 |          200 |  1 |
|        B |          1 |         2000 |  1 |
|        B |          2 |          300 |  1 |
|        C |          1 |          900 |  1 |
|        C |          2 |          600 |  1 |

After I saw your result found another method

Second DEMO

SELECT *
FROM (
        SELECT 
            t1.`FacilityID`,
            t1.`ChemicalID`, 
            t1.`ZoneType`, 
            t1.`ZoneDistance`,
             ( SELECT COUNT(*)
               FROM Facility as t2
               WHERE t1.`FacilityID` = t2.`FacilityID`
                 AND ( ( t1.`ZoneDistance` < t2.`ZoneDistance`)
                    OR ( t1.`ZoneDistance` = t2.`ZoneDistance` and t1.`ZoneType` > t2.`ZoneType`)
                    OR ( t1.`ZoneDistance` = t2.`ZoneDistance` and t1.`ZoneType` = t2.`ZoneType` and t1.`ChemicalID` > t2.`ChemicalID`)
                     )
             ) as rn
        FROM       Facility AS t1
        ORDER BY 
            t1.`FacilityID`,        
            t1.`ZoneDistance` DESC,
            t1.`ZoneType`, 
            t1.`ChemicalID`
    ) T
WHERE rn = 0;

OUTPUT

FacilityID  ChemicalID  ZoneType    ZoneDistance    rn
2           13          C           600             0
1           7           B           2000            0

score 0 · Accepted Answer · answered Jun 03 '16 at 17:28

Select f.FacilityID, 
     f.ZoneType,
     m.MaxZoneDistance
from AllZones f
     join (
          SELECT max(ZoneDistance) MaxZoneDistance,
               FacilityID 
          FROM AllZones 
          group by FacilityID
          ) m
          on f.facilityID=m.facilityID 
               and MaxZoneDistance=ZoneDistance

OUTPUT

FacilityID  ZoneType    MaxZoneDistance
1           B           2000
2           C           600

GIS SQL Query Logic Syntax

2 Answers2