Subscribe to this thread
Home - General / All posts - Selecting % within.
antoniocarlos

582 post(s)
#31-Mar-21 13:40

Hi

How do I select areas within areas using a percentage? So I want to select all watersheds that fall 75% or more within a particular municipality. I know I can do an overlay intersect and do the proper calculations but I want to do it using a selection query.

Attachments:
Watersheds_Selection_9.mxb


How soon?

adamw


9,588 post(s)
#31-Mar-21 13:45

Select all watersheds that fall 75% or more within *which* municipality? The watershed table has a field named HU_12_NAME, with values, the municipalities table has a field named HU_10_NAME, without values, are these meant to be the same thing (usable in a join)? Or what is the criteria? If a watershed falls into multiple municipalities, which one to pick - the one with the biggest overlap?

antoniocarlos

582 post(s)
#31-Mar-21 14:27

Select all watersheds that fall 75% or more within *which* municipality?

-Every one. (so there are 78)

The watershed table has a field named HU_12_NAME,

-this is just the name of the watershed.

with values, the municipalities table has a field named HU_10_NAME, without values

-(ignore this field sorry I left it there)

,are these meant to be the same thing (usable in a join)?

I used this field name in an overlay

Or what is the criteria? If a watershed falls into multiple municipalities, which one to pick - the one with the biggest overlap?

Yes. So the idea is that is that if 75% (this # could change to 80% or whatever) of the watershed falls within the municipality. The municipality can develop (should develop ) a watershed management plan.

---The question is: What watersheds occupy 75% or more area of each (within) municipality. Some municipalities do not meet this criteria because they are split between multiple watersheds.

Thanks


How soon?

antoniocarlos

582 post(s)
#31-Mar-21 14:57

Maybe a visual

Attachments:
Capture.JPG


How soon?

yves61
248 post(s)
#31-Mar-21 15:45

You are lucky I was just digging into this. Maybe this could help as a hint :

source : watersheds

overlay: municipality

select a.*, geomArea(s_geom,0) / geomArea(o_geom,0) as pct from Call GeomOverlayTopologyIdentity(source, overlay, 0) as a

Further on still needing to Group By and sum(), and check for NULL values with overlay fields (o_ )

yves61
248 post(s)
#31-Mar-21 16:26

You could use this : naming has been modified

select a.*, geomArea([s_Geom (I)],0) / geomArea( [o_Geom (I)],0) as pct from Call GeomOverlayTopologyIdentity(Watersheds_Hu12, Municipios, 0) as a

--WHERE

--o_mfd_id IS NOT NULL

antoniocarlos

582 post(s)
#31-Mar-21 16:40

Thanks to all! I'll try these options and report.


How soon?

yves61
248 post(s)
#31-Mar-21 17:55

Sorry, the code in my previous response is wrong while calculating the percentage of each part of watershed offset against the municipality area. It needed to calculate each watershed part area within a municipality against the total area of the watershed.

I modified my code, following Adam's query down here, but keeping the ' temporary table' within a subquery while also adding the municipality name

---

SELECT

w.*

, wm.o_Municipio

, RoundDecs( GeomArea([s_Geom (I)],0),0) as s_geom_area

, RoundDecs( GeomArea([Geom (I)],0),0) as geom_area

, GeomArea([s_Geom (I)],0) / GeomArea([Geom (I)],0) as PCT

FROM

Watersheds_Hu12 as w

INNER JOIN

(

SELECT * FROM CALL GeomOverlayTopologyIdentity(Watersheds_Hu12, Municipios, 0)

) as wm

ON

w.mfd_id = wm.s_mfd_id

WHERE

GeomArea([s_Geom (I)],0) / GeomArea([Geom (I)],0) >= 0.75 -- (remark: Pct >= xxx )

---

antoniocarlos

582 post(s)
#31-Mar-21 21:33

This works perfectly. Thanks.


How soon?

adamw


9,588 post(s)
#31-Mar-21 16:48

Exactly, I would do the same thing.

First, split each watershed into parts by municipio and put the result into a temporary table:

--SQL9

SELECT * INTO [watersheds_by_municipios]

FROM CALL GeomOverlayTopologyIdentityPar([Watersheds_Hu12][Municipios],

  0, ThreadConfig(SystemCpuCount()));

Then select just those watersheds which have a part that is 75% of the total area or bigger:

--SQL9

SELECT w.*

FROM [Watersheds_Hu12] AS w INNER JOIN [watersheds_by_municipios] AS wm

  ON w.[mfd_id]=wm.[s_mfd_id]

WHERE GeomArea(wm.[s_Geom (I)], 0) / GeomArea(w.[Geom (I)], 0) * 100 >= 75;

The result table of the second SELECT has no unique indexes, but we know from the definition of the task that each watershed can only participate once (there can be only one part with 75% or more area). So, we can, for example, alter this second SELECT to do SELECT INTO and then add an index on the MFD_ID field manually (Add Identity button in the Schema dialog should do the trick).

Dimitri


6,560 post(s)
#31-Mar-21 15:05

If I understand the task correctly, it seems to me this is similar to the example of finding percentages of open space within zip code areas.

You'd cut all watersheds by all municipalities the same way the example cuts all open spaces by all zipcodes. For each watershed piece you want to retain the initial area of the watershed and the watershed ID. For each piece you can calculate the area. Any piece where the size of the piece is >75% of the initial watershed size is an example of a watershed where 75% or more of it falls within that municipality.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.