[mapguide-users] Display Historival value assigned to a polygon

[Liglio]

I am trying to display historical info about States of a Country. I have two
tables: State (State ID, State Name, Geometry) and State Power Load (State
ID, Date/Time, Power Observed, Power Predicted). To show a State color
themed layer based on the deviation of Observed and Predicted, I need to
join these two tables in a single one table (State ID, State Name,
Date/Time, Power Observed, Power Predicted, Geometry). This solution will
lead to a large table due to the geometry of the State to be in each row
(so, I don't like it). Another solution is to join two tables using
mapguide, but I already tried some years ago and it did not work (Postgresql
database).

What is the best approach to solve this problem ?




--
Sent from: http://osgeo-org.1560.x6.nabble.com/MapGuide-Users-f4182607.html

[Martin Morrison]

Why not do the join in a database view? Then let Mapguide use the view for display?

Martin Morrison     
Infrastructure Application Engineer/Systems Analyst
 Engineering Design Systems, Inc.
540.345.1410
***@edsi.com

 
www.edsi.com

-----Original Message-----
From: mapguide-users [mailto:mapguide-users-***@lists.osgeo.org] On Behalf Of Liglio
Sent: Wednesday, January 3, 2018 6:39 PM
To: mapguide-***@lists.osgeo.org
Subject: [mapguide-users] Display Historival value assigned to a polygon

I am trying to display historical info about States of a Country. I have two
tables: State (State ID, State Name, Geometry) and State Power Load (State ID, Date/Time, Power Observed, Power Predicted). To show a State color themed layer based on the deviation of Observed and Predicted, I need to join these two tables in a single one table (State ID, State Name, Date/Time, Power Observed, Power Predicted, Geometry). This solution will lead to a large table due to the geometry of the State to be in each row (so, I don't like it). Another solution is to join two tables using mapguide, but I already tried some years ago and it did not work (Postgresql database).

What is the best approach to solve this problem ?

[Liglio]

If I use a view I will have the problem that a view has no identifier, and
mapguide can't show the features. I am using Postgresql, I already tried to
use FDO schema overrides but the tool gave some errors.

Maybe I can assign the layer to a blank table and insert some features just
for the user map session ?



--
Sent from: http://osgeo-org.1560.x6.nabble.com/MapGuide-Users-f4182607.html

[RenoSun]

I have been using spatial views with by editing the Configuration Document of
the database data connection, and it works with SQL Server 2008 and 2016...

Also, the version of the Maestro may cause the problems too.

I am using AIMS 2017 with Maestro 6.0m7 without error messages when I am
trying to edit the Configuration Document of the database data connection.

However, I got error messages when I was using Maestro 6.0m8.

You may want to just create a temporary database, and create a table with
the fields that you would like to use for your spatial view. Then, use FDO
Schema Override Tool to extract the correct Configuration XML for your
spatial view...

Here is my example of the Configuration XML for DB data connection... but we
must have different coordinate system, and FDO provider... so please modify
them to fit your environment.

Change YOUR_GEOM_NAME to the geometry field name that you're using, and
SPATIAL_VIEW_NAME to your spatial view name.

<?xml version="1.0" encoding="UTF-8"?>
<fdo:DataStore xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xlink="http://www.w3.org/1999/xlink"
xmlns:gml="http://www.opengis.net/gml"
xmlns:fdo="http://fdo.osgeo.org/schemas"
xmlns:fds="http://fdo.osgeo.org/schemas/fds">
<gml:DerivedCRS gml:id="Default">
<gml:remarks>Spatial context from SqlServer metadata</gml:remarks>
<gml:srsName>Default</gml:srsName>
<gml:validArea>
<gml:boundingBox>
<gml:pos>-2000000.000000 -2000000.000000</gml:pos>
<gml:pos>2000000.000000 2000000.000000</gml:pos>
</gml:boundingBox>
</gml:validArea>
<gml:baseCRS
xlink:href="http://fdo.osgeo.org/schemas/feature/crs/#UTM84-10N" />
<gml:definedByConversion
xlink:href="http://fdo.osgeo.org/coord_conversions#identity" />
<gml:derivedCRSType
codeSpace="http://fdo.osgeo.org/crs_types">geographic</gml:derivedCRSType>
<gml:usesCS xlink:href="http://fdo.osgeo.org/cs#default_cartesian" />
</gml:DerivedCRS>
<gml:DerivedCRS gml:id="sc_5">
<gml:remarks>Spatial context from SqlServer metadata</gml:remarks>
<gml:srsName>sc_5</gml:srsName>
<gml:validArea>
<gml:boundingBox>
<gml:pos>390000.000000 5464000.000000</gml:pos>
<gml:pos>400000.000000 5469000.000000</gml:pos>
</gml:boundingBox>
</gml:validArea>
<gml:baseCRS
xlink:href="http://fdo.osgeo.org/schemas/feature/crs/#UTM84-10N" />
<gml:definedByConversion
xlink:href="http://fdo.osgeo.org/coord_conversions#identity" />
<gml:derivedCRSType
codeSpace="http://fdo.osgeo.org/crs_types">geographic</gml:derivedCRSType>
<gml:usesCS xlink:href="http://fdo.osgeo.org/cs#default_cartesian" />
</gml:DerivedCRS>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://fdo.osgeo.org/schemas/feature/dbo"
xmlns:fdo="http://fdo.osgeo.org/schemas"
xmlns:gml="http://www.opengis.net/gml"
xmlns:dbo="http://fdo.osgeo.org/schemas/feature/dbo"
elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="SPTIAL_VIEW_NAME" type="dbo:SPTIAL_VIEW_NAMEType"
abstract="false" substitutionGroup="gml:_Feature">
<xs:key name="SPTIAL_VIEW_NAMEKey">
<xs:selector xpath=".//SPTIAL_VIEW_NAME" />
<xs:field xpath="Asset_ID" />
</xs:key>
</xs:element>
<xs:complexType name="SPTIAL_VIEW_NAMEType" abstract="false"
fdo:geometryName="YOUR_GEOM_NAME">
<xs:annotation>
<xs:documentation />
<xs:appinfo source="http://fdo.osgeo.org/schemas" />
</xs:annotation>
<xs:complexContent>
<xs:extension base="gml:AbstractFeatureType">
<xs:sequence>
<xs:element name="Asset_ID" minOccurs="0">
<xs:annotation>
<xs:documentation />
</xs:annotation>
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="MI_PRINX">
<xs:simpleType>
<xs:restriction base="xs:int" />
</xs:simpleType>
<xs:annotation>
<xs:documentation />
</xs:annotation>
</xs:element>
<xs:element name="YOUR_GEOM_NAME"
type="gml:AbstractGeometryType" fdo:hasMeasure="false"
fdo:hasElevation="false" fdo:srsName="sc_5" fdo:geometricTypes="point curve
surface " fdo:geometryTypes="point multipoint linestring multilinestring
curvestring multicurvestring polygon multipolygon curvepolygon
multicurvepolygon ">
<xs:annotation>
<xs:documentation />
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
</xs:schema>
<SchemaMapping xmlns:rdb="http://fdordbms.osgeo.org/schemas"
xmlns="http://www.autodesk.com/isd/fdo/SQLServerSpatialProvider"
textInRow="NotInRow" provider="OSGeo.SQLServerSpatial.3.8" name="dbo">
<complexType name="SPTIAL_VIEW_NAMEType" tableMapping="Concrete"
identityIsGloballyUnique="false" identitySeed="0" identityIncrement="0"
identityPropertyName="">


<element name="Asset_ID">
<Column name="Asset_ID" />
</element>
<element name="MI_PRINX">
<Column name="MI_PRINX" />
</element>
<element name="YOUR_GEOM_NAME">
<GeometricColumn name="YOUR_GEOM_NAME" />
</element>
</complexType>
</SchemaMapping>
</fdo:DataStore>



--
Sent from: http://osgeo-org.1560.x6.nabble.com/MapGuide-Users-f4182607.html

[Hans Milling]

I usually create a table that matches the view (with a primary key). Then I
add the FeatureSource pointing to the table. The feature source is created
with primary key and can be selected. Then I delete the table and create a
vire with the same name, and everything works.



--
Sent from: http://osgeo-org.1560.x6.nabble.com/MapGuide-Users-f4182607.html

[Liglio]

Thanks to RenoSun and Hans for your help,

I used the Hans solution becouse it appears to be easier, and it works. Very
clever.



--
Sent from: http://osgeo-org.1560.x6.nabble.com/MapGuide-Users-f4182607.html

[RenoSun]

I agreed with Martin Morrison.

I will join two tables with PostgreSQL as one spatial view table.

If you would like to enable the selection for spatial view, you may check
out following link:

http://osgeo-org.1560.x6.nabble.com/SQL-Server-Spatial-View-Selection-Issue-td5228068.html

However, if you really dislike the large joint table...

I will develop a PHP, and ASP .NET etc. web page to query and display the
*State Power Load* table info based on the *State ID*.

Display the PHP/ASP .NET web page by map tip hyper link, opening the new
window, or task panel (Fusion).

Cheers!



--
Sent from: http://osgeo-org.1560.x6.nabble.com/MapGuide-Users-f4182607.html