ABOUT

DEVELOPERS

RESOURCES

Follow DMPInc on Twitter

Publish With Group By

Publish is the process of creating a new layer that is designed for high performance and availability for use in web applications, from a transactional layer. The publish process gives the application programmer a flexible way to create multiple layers, displaying different data from a single base layer. These layers can then be added to your application and kept current with subsequent publish calls which can be automated.

The layer definitions below create a new summary dataset that is aggregated by postal code and that includes average, max, min and sum values for the SALE_PRICE field. The result is then linked to the Postal Code data component from the SpatialStream® Content Library using PROP_ZIP as the key.

Two layer definitions are presented below. The first one contains a layer definition for the base data and the second one contains the layer definition for the published layer.

Base Layer Definition:



   1:  <?xml version="1.0" encoding="utf-8"?>
   2:  <Layer PublicName="DMP_LICENSE/LASALESDATAFORPUBLISH" Name="DMP_LICENSE/LASALESDATAFORPUBLISH" Type="WMS" StorageType="DB">
   3:      <Description>LA Sales Data 2010 for Publish Example</Description>
   4:      <GeometryType>POINT</GeometryType>
   5:      <IndexedFields Scope="private">PROP_ZIP,LANDUSE_DESC,SALE_PRICE,SALE_DATE</IndexedFields>
   6:      <Schema>
   7:          <ElementType name="Row">
   8:              <AttributeType name="LANDUSE_DESC" label="LANDUSE_DESC" abbrname="LANDUSE_DESC" editable="False" searchable="true" length="50">
   9:                  <Datatype type="string" />
  10:              </AttributeType>
  11:              <AttributeType name="SALE_DATE" label="SALE_DATE" abbrname="SALE_DATE" editable="False" searchable="true">
  12:                  <Datatype type="date" />
  13:              </AttributeType>
  14:              <AttributeType name="SALE_PRICE" label="SALE_PRICE" abbrname="SALE_PRICE" editable="False" searchable="true">
  15:                  <Datatype type="integer" />
  16:              </AttributeType>
  17:              <AttributeType name="PROP_ZIP" label="PRICEPERSQFT" abbrname="PRICEPERSQFT" editable="true" searchable="true" length="10">
  18:                  <Datatype type="string" />
  19:              </AttributeType>
  20:              <AttributeType name="LONGITUDE" label="LONGITUDE" abbrname="LONGITUDE" editable="true" searchable="true">
  21:                  <Datatype type="double" />
  22:              </AttributeType>
  23:              <AttributeType name="LATITUDE" label="LATITUDE" abbrname="LATITUDE" editable="true" searchable="true">
  24:                  <Datatype type="double" />
  25:              </AttributeType>
  26:          </ElementType>
  27:      </Schema>
  28:  </Layer>


Publish Layer:


Once the data exists within the base layer the publish layer is created using the SpatialStream® CreateLayer service with the layer definition below

Publish Layer Definition:

   1:  <?xml version="1.0" encoding="utf-8"?>
   2:  <Layer Name="DMP_LICENSE/LAAVERAGEPRICESOLD" StorageType="DOFS" PublicName="DMP_LICENSE/LAAVERAGEPRICESOLD">
   3:      <BaseResource Name="S">DMP_LICENSE/LASALESDATAFORPUBLISH</BaseResource>
   4:      <Description>La Sales Data Example</Description>
   5:      <IndexedFields>PROP_ZIP</IndexedFields>
   6:      <GeometryType>POLYGON</GeometryType>
   7:      <Schema>
   8:          <ElementType name="Row">
   9:              <AttributeType name="PROP_ZIP" label="Prop Zip" expression="S.PROP_ZIP"></AttributeType>
  10:              <AttributeType name="SOLDPRICE_SUM_L" label="Property Value Sum" expression="case when SUM(S.SALE_PRICE) is null then '' else PARSENAME(Convert(varchar,Convert(money,coalesce(convert(float,SUM(S.SALE_PRICE))/1000,0)),1),2)+'K' end"></AttributeType>
  11:              <AttributeType name="SOLDPRICE_AVG_L" label="Property Value Average" expression="case when AVG(S.SALE_PRICE) is null then '' else PARSENAME(Convert(varchar,Convert(money,coalesce(convert(float,AVG(S.SALE_PRICE))/1000,0)),1),2)+'K' end"></AttributeType>
  12:              <AttributeType name="SOLDPRICE_MIN_L" label="Property Value Minimum" expression="case when MIN(S.SALE_PRICE) is null then '' else PARSENAME(Convert(varchar,Convert(money,coalesce(convert(float,MIN(S.SALE_PRICE))/1000,0)),1),2)+'K' end"></AttributeType>
  13:              <AttributeType name="SOLDPRICE_MAX_L" label="Property Value Maximum" expression="case when MAX(S.SALE_PRICE) is null then '' else PARSENAME(Convert(varchar,Convert(money,coalesce(convert(float,MAX(S.SALE_PRICE))/1000,0)),1),2)+'K' end"></AttributeType>
  14:              <AttributeType name="SOLDPRICE_SUM" label="Property Value Sum" expression="case when SUM(S.SALE_PRICE) is null then -9999 else SUM(S.SALE_PRICE) end"></AttributeType>
  15:              <AttributeType name="SOLDPRICE_AVG" label="Property Value Average" expression="case when AVG(S.SALE_PRICE) is null then -9999 else AVG(S.SALE_PRICE) end"></AttributeType>
  16:              <AttributeType name="SOLDPRICE_MIN" label="Property Value Minimum" expression="case when MIN(S.SALE_PRICE) is null then -9999 else MIN(S.SALE_PRICE) end"></AttributeType>
  17:              <AttributeType name="SOLDPRICE_MAX" label="Property Value Maximum" expression="case when MAX(S.SALE_PRICE) is null then -9999 else MAX(S.SALE_PRICE) end"></AttributeType>
  18:              <AttributeType name="PROPERTY_CNT" label="Property Count" expression="count(S.SALE_PRICE)"></AttributeType>
  19:              <AttributeType name="SALEYEAR" label="Sale Year" expression="year(S.SALE_DATE)"></AttributeType>
  20:              <AttributeType name="SALEMONTH" label="Sale Month" expression="month(S.SALE_DATE)"></AttributeType>
  21:              <AttributeType name="LANDUSE_DESC" label="Landuse" expression="S.LANDUSE_DESC"></AttributeType>
  22:          </ElementType>
  23:      </Schema>
  24:      <GroupBy>S.PROP_ZIP, S.LANDUSE_DESC, YEAR(S.SALE_DATE), MONTH(S.SALE_DATE)</GroupBy>
  25:      <Filter>MONTH(S.SALE_DATE) = 9 and year(S.SALE_DATE) = 2010 and LANDUSE_DESC = 'SFR'</Filter>
  26:      <GeometryResource>POSTALCODELINK</GeometryResource>
  27:      <Links PATH="$(FULL_PATH)Links/LASALESAVERAGEPRICESOLD.xml">
  28:          <Link Name="POSTALCODELINK" Class="Dmp.Neptune.Links.KeyLink" ToResource="DMP_LICENSE/POSTALCODE">
  29:              <Operation>Equals</Operation>
  30:              <Pk>PROP_ZIP</Pk>
  31:              <Fk>POSTCODE</Fk>
  32:          </Link>
  33:      </Links>
  34:  </Layer>
 

Items to note within the publish layer definition

  1. Line #2 defines the layer name and the folder where the data will be published to. In this case folder = DMP_LICENSE and layer name = LAAVERAGEPRICESOLD.
  2. Line #3 defines where the base data will be pulled from.
  3. Line #5 is grouping the data based on postal code and using that field as a key field to link it back to the Postal Code Content Library dataset. Because we will be using that field as a key we want to have the service index that field for the best performance.
  4. Line #10 - #13 use a format operation so that the data is formatted. These fields are used in the example when displaying the data in the balloon.
  5. Line #14 - #17 output the raw values that are used in the SLD definition for thematic display.
  6. Line #24 defines the group by statements to group the data.
  7. Line #25 defines the filter that will be used. Using this approach the base data can be kept up to date using Data Synchronization techniques and data can be published on a regular bases and keep the published dataset up to date.
  8. Line #26 defines the link name that will be used to link the published data to a spatial dataset.
  9. Line #27 defines that actual link where in this case it is linking to the DMP_LICENSE/POSTALCODE dataset using the primary key PROP_ZIP field located in the base data and foreign key POSTCODE field that is located in the DMP_LICENSE/POSTALCODE dataset.

The LayerCreate service is used passing in the layer definition to create the layer within your account. Once the layer has been created the Publish service is called to publish data into that layer. As data is updated in the Base Layer the Publish service is called to re publish the dataset to include the new data.

You can see an example of the result here.