importing demands into an existing model from excel data

Hi Travis,

It looks like Mark and Jesse have provided some good options, but here's a way to do this that's closer to what I believe was your initial approach.

Sample Excel data:

In WaterGEMS, create a user data field (Tools>User Data Extensions) called customer demand with units of gpm (or gpd, or whatever you prefer):

Use Modelbuilder to import junction nodes, coordinates, and customer demand into your user data field:

Customize a junction FlexTable to include your Customer Demand field, and use the button in the upper left of the table to export the shapefile. You can then use the shapefile as the source of customer locations and demands when you set up LoadBuilder in your actual model.

Regards,

Kristen Dietrich

Thanks so much Kristen,

I've been messing around with this just testing it out and I think this will work fine using the Theissen Polygon and importing the shape file using nearest node.

Travis

The simplest way is to just use ModelBuilder?  Importing Coordinates AND Demands at the same time can be done out of the box!

In ModelBuilder you can set it up to use the same worksheet twice (or 3,4.....how ever many times you like) in the SAME ModelBuilder import using a little known feature.   All you need to do the below is to have a column named "Row Number" or "Row Label" etc.  which can just be 1,2,3,4.... in Excel for the different junctions.

The "trick" is back on the first step of ModelBuilder.......there is a button marked "Duplicate" underneath "Choose the tables you would like to work with"  Hit "Duplicate" with the worksheet containing the data selected, so that it appears twice in the ModelBuilder data table list:

1.  One link for the Junction Label, X and Y (Table Type: Junction)

2.  In the same ModelBuilder operation, another duplicate data link for the Junction Demand Collections (Table Type:Junction, Demand Collection)

Go forward in the wizard to the next step and you will see the same worksheet similarly appear twice.  Setup the first data link for X and Y coordinate import under Table Type: Junction

Similarly click down onto the second, duplicated link of the data worksheet and setup this as Type Type: Junction, Demand Collection (both links will need the Key Field specified identically as eg. The row number label.)

Import and voila! Junctions with both coordinates AND demands imported!

Thanks a ton Ben,  That worked perfectly to import the demands.  What I've realized is that I now have the demands imported but no way of exporting them via the junction flextable.

It will not let you export the "Demand Collection" field.  It will allow you to export the demand field.  The only way to get the correction demands is to have pipes connecting all the nodes and have a reservoir.  I will have to add piping to all the junctions/nodes to be able to use this method I believe.

Let me add one more option here.

Reading the original post, I feel like this is the exact reason why I wrote this tool. The tool can take .CSV (which you can save from an Excel) file and will give you a shapefile so that you can use it loadbuilder. (if needed same shapefile can be used to build the model)

http://communities.bentley.com/other/old_site_member_blogs/peer_blogs/b/akshayas_blog/archive/2013/02/05/csv-to-shapefile-a-waterobjects-net-tool.aspx

There is a little video too to help the conversion process.

Let me add one more option here.

Reading the original post, I feel like this is the exact reason why I wrote this tool. The tool can take .CSV (which you can save from an Excel) file and will give you a shapefile so that you can use it in loadbuilder. (if needed same shapefile can be used to build the model)

http://communities.bentley.com/other/old_site_member_blogs/peer_blogs/b/akshayas_blog/archive/2013/02/05/csv-to-shapefile-a-waterobjects-net-tool.aspx

There is a little video too to help the conversion process.

Akshaya,

Thanks for the link.  Jesse posted a link to your tool previous in the thread.  I haven't had a chance to try it yet as my I.T. dept has yet to copy the exe to the proper directory.  Also for some reason when I try to watch the video nothing shows up.  I can see the preview and as soon as I click play, the whole video disappears.

Akshaya,

Thanks for the link.  Jesse posted a link to your tool previous in the thread.  I haven't had a chance to try it yet as my I.T. dept has yet to copy the exe to the proper directory.  Also for some reason when I try to watch the video nothing shows up.  I can see the preview and as soon as I click play, the whole video disappears.

Hi All,

I am trying to use model builder for importing demands into Unit demand control centre.  I am using Bentley WaterGems V8i Select Series4 and this is not giving me th option of using "Junction,Demand Collection" as Table Type.  Has anyone else encountered this and what is way around this to import my unit demands from a shapefile into Unit Demand Control Centre.

Hi RDD,

That's right.  ModelBuilder treats spatial data source types like SHP and CAD a little differently, because ModelBuilder will also import/export the spatial geometry data for eg. Junctions and Pipes.   These data types are used for laying out the spatially connected network of nodes and pipes.

The ONLY WaterCAD Modelling Elements that have geometry data are elements like Junction Elements and Pipe Elements, so these are all that will appear as available Table Types to import.  Component and Collection Table Types do not have spatial geometries, so do not appear on the list.

For Shape files, you have a way around this though.   For this data type,  all the geometry data is kept in the *.SHP file...........whilst all the attribute data is kept in the dBase *.DBF file.

So, if you want to use data in Shape Files for things like the Unit Demand Collection or the Demand Collection, in ModelBuilder you instead pick the "dBase IV" Data Source Type, instead of the "Esri Shapefiles" Data Source Type in the Wizard's Dialog.   You will need to specify some sort of unique ID field that is the same as for when importing the SHP data for the Junction coordinates, or the DBF data for eg. Junction, Demand Collection data to make sure the right Demands go to the right Junctions.

Thanks Ben,

That explains it and it did worked.

Hello everybody,

I think a face a similar problem.

I want to model a specific DMA with a single inlet. Unfortunately, my company does not use ArcGIS for our huge network but Smallworld GIS (for some reasons). That means I can not have shapefiles. Nevertheless, I have access to dxf archives showing the position of buildings and the number of connections for each building. Besides, we may have excel showing billing meter data (address, etc) for each connection for the last 3 years.

I have modeled the whole DMA and now I am at the last stage of demand allocation. It looks difficult for me to use 'billing meter aggregation method' because of lack of geocoded billing meter data. I was thinking to devide the whole metered consumption to the number of connections I have and then to share them to all of my connections.

What is your opinion?

How do you recommend me to proceed having in mind that I am looking for a well calibrated model.

Thank you in advance.  

Antonio

Do you have a way of getting a gps or an x,y coordinate for each address?  If so you could easily use Loadbuilder ot assign the appropriate loads to each node.  I'm not sure how you could import and distribute the loads properly without a spatial reference.

If you try to use Modelbuilder, it will all the demands but all junctions will be stacked ontop one another not creating the correct spatial distribution.

You could try using the address's you have and get the geocode info from one of the free sites.

www.gpsvisualizer.com/geocoding.html

http://geocoder.us/

You always have to question the accuracy of these services...