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

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.

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

I've been doing some searching and ran across this site.  I've tested this out on my own and it works surprisingly well using Google's geocoding api.

This guy created an excel function using visual basic to call out to google's geocoding api and return the latitude and longitude of a specific address.  I was surprised how well it worked.

policeanalyst.com/using-the-google-geocoding-api-in-excel