[mapguide-users] Help with joining tables please

[Darren Karasiuk]

Well, I created a view with only the native table selected and I cannot
choose the lat/lon column. Those columns are still considered keys.
You’re right Dave, doesn’t make much sense to have a Float as a key.

Any other ways to join tables?  Two methods so far don’t work for me.

Darren

________________________________________
From: mapguide-users-bounces at lists.osgeo.org
[mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Dave Wilson
Sent: March 26, 2009 5:24 PM
To: MapGuide Users Mail List
Subject: RE: [mapguide-users] Help with joining tables please

That is correct the view likely won’t indicate the key, but as long as the
column is available to pick and you select it it should work.

I don’t think we support the other numeric datatypes for key columns
although arguably tinyint and bigint should be candidates. Why float shows
up as a key column candidate for the table is odd. Who would use a float as
a key?

I haven’t worked as much with SQL Server 2008 so the behavior of the views
is unfamiliar to me.

Dave

From: mapguide-users-bounces at lists.osgeo.org
[mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Darren Karasiuk
Sent: Thursday, March 26, 2009 5:08 PM
To: 'MapGuide Users Mail List'
Subject: RE: [mapguide-users] Help with joining tables please

I updated the FDO to 3.3.2 (I can see the SQL Server Spatial type now!), but
I still cannot select the lat/lon fields.
All but five of the columns are “keys”.
Each table in the database has primary keys, indexes, and one even has
functions. (I’m not using any functions at this time.)
I have the primary key from the first native table (before all the inner
joins) selected (and always has been).
As far as I can tell, the View doesn’t indicate keys, is that right? 
Although in the designer it is clear which columns are keys.

Okay, a little more digging, and I discovered those five columns are all
data type “int”. The other 34 are “tinyint”, “bigint”, “float”, “datetime”,
and “varchar”. Is there a connection here? Why isn’t the “float” of my
lat/lon being picked up in the View but it does as a native table?

Tomorrow I’ll start my view from one table and see if that works then build
my query up to what I want.

________________________________________
From: mapguide-users-bounces at lists.osgeo.org
[mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Dave Wilson
Sent: March 26, 2009 4:30 PM
To: MapGuide Users Mail List
Subject: RE: [mapguide-users] Help with joining tables please

The native table will need a primary key column in order to support
selection as well as to provide a key for the view and support selection
from the view.

So what you should have is some column from Table A defined as a primary
key. Include that column in the view.

Ignore my last question. I got confused.

Dave


From: mapguide-users-bounces at lists.osgeo.org
[mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Darren Karasiuk
Sent: Thursday, March 26, 2009 3:44 PM
To: 'MapGuide Users Mail List'
Subject: RE: [mapguide-users] Help with joining tables please

All of my mapping works fine using the data I have in the database. The main
table has GPS positions as a float as well as a SQL Server 2008 Geography
column. I have used the lat/lon columns as point data on the map and themed
very nicely. However, I need extra information from other other tables that
will be used for more theming, extra info, highlighting roads that have
speed violatations, etc.

For some reason, the lat/lon columns are showing as “key” fields in Mag
Guide Studio _only_ for the newly created view.  In the View Designer, I
have no keys set up for these columns in either the view OR the native
table.

I’m not quite sure what you mean in the last question. I am using the ODBC
provider to connect to the database. I am not using the spatial provider for
this layer.  I use shape files uploaded to the repository to which the GPS
points in my lat/lon columns line up along nicely.  One thought occurs to me
now, should I update the FDO? I am using the out-of-the-box version.  I’ll
check if there is an updated version and try again.

________________________________________
From: mapguide-users-bounces at lists.osgeo.org
[mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Dave Wilson
Sent: March 26, 2009 3:29 PM
To: MapGuide Users Mail List
Subject: RE: [mapguide-users] Help with joining tables please

What datatype is your GPS_LAT/LON fields?

You have to click the Geometry check box (in Studio) in order to set the X
and Y columns (these aren’t key columns).

So is your data just point data then and you are using the ODBC Provider for
both sources?

Dave

From: mapguide-users-bounces at lists.osgeo.org
[mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Darren Karasiuk
Sent: Thursday, March 26, 2009 3:07 PM
To: 'MapGuide Users Mail List'
Subject: RE: [mapguide-users] Help with joining tables please

Hi Dave, thank you for the suggestions. I am using a single database and
trying to join tables within them.
I agree that using a View is the best choice, because that also allows me to
adjust time zones.

The problem I’m having now is MapGuide Studio will NOT display my GPS_LAT
and GPS_LON fields when setting up the geometry. Out of 39 columns, only
five are selectable.
One thing that appears different is for the native table, only two columns
are listed as “key” fields and they are not the GPS_LAT/LON columns.  My new
view has almost all of the columns as a “key” field including the lat/lon
columns.
How can I move the “key” fields to the selectable x, y columns?

Darren

________________________________________
From: mapguide-users-bounces at lists.osgeo.org
[mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Dave Wilson
Sent: March 26, 2009 8:39 AM
To: MapGuide Users Mail List
Subject: RE: [mapguide-users] Help with joining tables please

Of course if both tables are in the same database instance you might get
your DBA to create a view joining the two tables for you. The performance
would be better.

Dave

From: mapguide-users-bounces at lists.osgeo.org
[mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Dave Wilson
Sent: Wednesday, March 25, 2009 5:23 PM
To: MapGuide Users Mail List
Subject: RE: [mapguide-users] Help with joining tables please

Can we assume your spatial data is in tableA and you are using a Spatial
provider connection? For table B I would suggest using an ODBC connection
via a DSN. You don’t need the overhead of the spatial provider stuff for the
joined table.

The same should apply when joining to SDF. In almost all cases the join
should use an ODBC connection to the secondary data.

Dave

From: mapguide-users-bounces at lists.osgeo.org
[mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Darren Karasiuk
Sent: Wednesday, March 25, 2009 4:47 PM
To: 'MapGuide Users Mail List'
Subject: [mapguide-users] Help with joining tables please

Hello, I’ve been fighting with this for a while and need your help.

I want to combine a few tables together and have that data available for
themeing and viewing.
Without attempting to join tables, the data loads without any problem.
However, I have not been able to get tables joined.

Using normal SQL Server 2008 “inner join tableB on tableA.location =
tableB.location” is what I’m trying to do.
Using the Autdesk Mapguide Studio, I click through, set up my joining tables
exactly as I would in SQL.
In the layer definition editor, I see the new attributes.
Before doing anything else, I export the layer as XML and test my map.  The
map viewer doesn’t stop showing the busy icon, the legend labels become
question marks (?) and no data ever loads.  If I disable the layer, all the
other layers show quickly.

I’ve tried using the same database connection, a different database
connection, and a SDF imported from a shapefile. No method appears to work
for me.

Is there a proper way of setting up joining tables? Should I learn the XML
syntax and do it manually?

Thanks for your help,

Darren