Combining road and tramway traffic

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Combining road and tramway traffic

siberrian
Hello! Is there any way to combine SQL functions BR_EvalSource and BTW_EvalSource (tramway traffic) into one source power? I would like to create a noise map, and on some road segments, there is road traffic but also parallel tramway traffic...  Or do i have to crate separare noise maps for roads and trams? Thank you
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Combining road and tramway traffic

Nicolas F.
Administrator
Hi,

You have two options.

 - Create two same linestring. One with road noise source and other with tram noise source
 - Do an energetic sum of the two sources on each frequency band. Energetic sum is 10*LOG10(POWER(10, ROAD_DB/10) + POWER(10, TRAM_DB/10)) DB_TOT .

The first option is easier but duplicate sound source number.

kind regards,

-Nicolas
Atelier SIG
IRSTV FR CNRS 2488
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Combining road and tramway traffic

siberrian
This post was updated on .
Thanks for you answer, i am trying my best to uderstand your advice, but i am a beginner.

I have this table for tramway spectrum repartition :

drop table if exists trams_src;
CREATE TABLE trams_src AS SELECT the_geom,
BTW_SpectrumRepartition(100,db_m) as db_m100,
BTW_SpectrumRepartition(125,db_m) as db_m125,
BTW_SpectrumRepartition(160,db_m) as db_m160,
BTW_SpectrumRepartition(200,db_m) as db_m200,
BTW_SpectrumRepartition(250,db_m) as db_m250,
BTW_SpectrumRepartition(315,db_m) as db_m315,
BTW_SpectrumRepartition(400,db_m) as db_m400,
BTW_SpectrumRepartition(500,db_m) as db_m500,
BTW_SpectrumRepartition(630,db_m) as db_m630,
BTW_SpectrumRepartition(800,db_m) as db_m800,
BTW_SpectrumRepartition(1000,db_m) as db_m1000,
BTW_SpectrumRepartition(1250,db_m) as db_m1250,
BTW_SpectrumRepartition(1600,db_m) as db_m1600,
BTW_SpectrumRepartition(2000,db_m) as db_m2000,
BTW_SpectrumRepartition(2500,db_m) as db_m2500,
BTW_SpectrumRepartition(3150,db_m) as db_m3150,
BTW_SpectrumRepartition(4000,db_m) as db_m4000,
BTW_SpectrumRepartition(5000,db_m) as db_m5000 from trams_src_global;

And similar table for road noise repartition.

How exactly do i use 10*LOG10(POWER(10, ROAD_DB/10) + POWER(10, TRAM_DB/10)) DB_TOT  for computing total energy sum of those two tables?

Or, I have ArcMap, can i use some function in Arc Map to comupte total energy sum?

Do I have to create new table (for example drop table if exists tramsroads_src...) and use that final table with summed repartition in creating sound propagation?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Combining road and tramway traffic

Nicolas F.
Administrator

Hi,

The energetic sum must be done on spectrum repartition:

```sql

drop table if exists src;
CREATE TABLE src AS SELECT the_geom,
10*LOG10(POWER(10, BTW_SpectrumRepartition(100,tram_db_m) / 10) + POWER(10, BR_SpectrumRepartition(100,road_db_m) / 10)) as db_m100,
10*LOG10(POWER(10, BTW_SpectrumRepartition(125,tram_db_m) / 10) + POWER(10, BR_SpectrumRepartition(125,road_db_m) / 10)) as db_m125,
...
from src_global;

```

The table src_global should have such fields

THE_GEOM, road_db_m, tram_db_m

-Nicolas

Le 2015-01-29 19:11, siberrian [via OrbisGIS] a écrit :

Thanks for you answer, i am trying my best to uderstand your advice, but i am a beginner.

I have this table for tramway spectrum repartition :

drop table if exists trams_src;
CREATE TABLE trams_src AS SELECT the_geom,
BTW_SpectrumRepartition(100,db_m) as db_m100,
BTW_SpectrumRepartition(125,db_m) as db_m125,
BTW_SpectrumRepartition(160,db_m) as db_m160,
BTW_SpectrumRepartition(200,db_m) as db_m200,
BTW_SpectrumRepartition(250,db_m) as db_m250,
BTW_SpectrumRepartition(315,db_m) as db_m315,
BTW_SpectrumRepartition(400,db_m) as db_m400,
BTW_SpectrumRepartition(500,db_m) as db_m500,
BTW_SpectrumRepartition(630,db_m) as db_m630,
BTW_SpectrumRepartition(800,db_m) as db_m800,
BTW_SpectrumRepartition(1000,db_m) as db_m1000,
BTW_SpectrumRepartition(1250,db_m) as db_m1250,
BTW_SpectrumRepartition(1600,db_m) as db_m1600,
BTW_SpectrumRepartition(2000,db_m) as db_m2000,
BTW_SpectrumRepartition(2500,db_m) as db_m2500,
BTW_SpectrumRepartition(3150,db_m) as db_m3150,
BTW_SpectrumRepartition(4000,db_m) as db_m4000,
BTW_SpectrumRepartition(5000,db_m) as db_m5000 from trams_src_global;

And similar table for road noise repartition.
How do i use 10*LOG10(POWER(10, ROAD_DB/10) + POWER(10, TRAM_DB/10)) DB_TOT  for computing total energy sum of those two tables?

Do I have to create new table (for example drop table if exists tramsroads_src...) and use that final table with summed repartition in creating sound propagation? 

-- 
Nicolas Fortin IRSTV FR CNRS 2488 GIS http://orbisgis.org Spatial DB http://h2gis.org Noise http://noisemap.orbisgis.org
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Combining road and tramway traffic

siberrian
Thank you very much! I will try that.

I also tried to just merge two tables into one by :

drop table if exists total;
CREATE TABLE total AS select * from roads_src UNION select * from trams_src;

It created a new same linestring, but it seems to work. Is it also correct method, the first you mentioned as easier?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Combining road and tramway traffic

Nicolas F.
Administrator

Yes this second option is correct. But use the UNION ALL query as it will not check for duplicate entries. (UNION removes duplicates, it's slow and it's a memory hog.)

regards,

Le 2015-01-30 12:00, siberrian [via OrbisGIS] a écrit :

Thank you very much! I will try that.

I also tried to just merge two tables into one by :

drop table if exists total;
CREATE TABLE total AS select * from roads_src UNION select * from trams_src;

It created a new same linestring, but it seems to work. Is it also correct method, the first you mentioned as easier?


If you reply to this email, your message will be added to the discussion below:
http://orbisgis.3871844.n2.nabble.com/Combining-road-and-tramway-traffic-tp7575226p7575231.html
To start a new topic under NoiseM@p, email [hidden email]
To unsubscribe from NoiseM@p, click here.
NAML

 

-- 
Nicolas Fortin IRSTV FR CNRS 2488 GIS http://orbisgis.org Spatial DB http://h2gis.org Noise http://noisemap.orbisgis.org
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Combining road and tramway traffic

siberrian
Thanks once again, i get it now, you have been very helpful.
Loading...