Populating from DB a TMapSeries/TWorldSeries

[Brace]

Hello,
I am trying to connect a TDBChart with a TMapSeries or TWorldSeries to a dataset.

I tried using the TDBChart editor in TWorldSeries/Series/Datasource and setting it to Dataset. But this doesn't work.

I perpared a sample program using a TVirtualTable that has a RegionID field and a TotalSalesID. I provided values for regions ID 1 2 3, the idea is to paint the regions 1 2 3 and leave the others empty.

I used a TMapSeries that on FormCreate loads an italian map (shp and shx files are included in the attachment) and I used a TWorldSeries sing the predefined Spain Map.

Now could you please provide the source code fo the 2 buttonclicks events to attach the dataset to the maps?

Thanks.

[Yeray]

Hi Brace,

I don't have VirtualTable so I can't run your application. But you shouldn't find too much problems if you are getting a list of values you want to assign to your regions.
The following example code seems to work fine here:

Code: Select all

procedure TForm4.Button2Click(Sender: TObject);
var i: Integer;
begin
  for i:=0 to Series1.Count-1 do
    Series1.ValuesList[2][i]:=0;

  Series1.ValuesList[2][0]:=500;
  Series1.ValuesList[2][1]:=200;
  Series1.ValuesList[2][2]:=300;

  DBChart1.Repaint;
end;

If you still find problems with it, please, tell us where we can download a trial of the VirtualTable component to run your application or try to arrange an example without it.

[Brace]

Hello,

I updated the example, just replace mainform.pas and dfm with the one included here.

I used BDE to simulate real data.

I am able to load data, anyway I hstill have 2 questions:
A) how to load the data into the maps without using the code? I mean is it not possible to connect to a datasource directly as in all the other DB components?
B) how to load (at least in TWorldSeries) data in regions that have more polygons (like islands in Spain)? Is There a quick way or I should manually load every single island?

[Yeray]

Hi Brace,

A) Yes it seems that, right now, when you create a Map series (Italy, for example) the polygons are automatically created, but if you try to assign a datasource to this series, the polygon list is cleared.
It would be nice if one could assign a datasource (at design time) to load values to the map. I've added this to the wish list to be considered for inclusion in future releases (TV52014834).

B) Yould identify all the polygons related to the same "region" or country comparing the label. You could do something similar to what was suggested here

[Brace]

Hello:

A) Does this mean that in practice for TMapSeries and TWorldSeries the DBcomponent makes currently no sense and all data must be assigned from code, right?

B) So a solution is to load the shapes and the labels, so in that procedure I will say that areas 5, 6, 7 (for example) has the same label (=they are the same region), then as I access the map to insert data I will refer to labels. So for example I say 5 6 7 are "NorthEast", so when I load data I check that if the label = NorthEast I set the value I like to set. Correct?

[Yeray]

Hi Brace,

Yes for both.

[Brace]

Thanks. I did some research on the Report Builder side. What I am working on is creating a reporting application that uses TMapSeries/TWorldSeries with ReportBuilder.
The only remaining problem I have now is how to manage the map file.
There are many ways to do, once is to store the files as BLOB in the DB, extract them on disk during the generation of report and loading the maps from file before report's preview.

So I should open a dataset containing a single map, put it in a memory stream and then loading the map from stream. Do you have an example of a similar procedure?

Is it possible to add maps to the TWorldSeries? Do you have an example?

Thanks.

[Yeray]

Hi Brace,

Here it was discussed something similar. There is an example about a datasource and it is explained how to edit the shp files.

[sicorspa]

hi all, I'm the same that have successfully loaded the maps from the resource file.

If you want I can post what I have done to load all, but can't help a lot in debug (haven't much time at work, and haven't instruments at home)..

here i have the first version of my program, with example table in dbf and map in shp, nothing store in the res file. If you want i can upload it, you must only create the database alias in the odbc

the one with res file is linked to the database so isn't stand-alone, but if you want to use the resource file i can post the code

[Brace]

posting won't harm

If you can post it would be great, because resources can be a solution for me, but I am looking for DB storing of maps, because if a user wants to create a report with a new map, he won't find it in the resources but it must be able to store it somewhere.

[sicorspa]

here is the first part of the project

in the successive versions i have added the use of the resources file, but isn't a stand alone project because after the attached version i have connected it to my database oracle, so it won't work outside

howether when i found the source code for the use of resource file I will post it

If I remember right, with the resource I use the loadFromStream function. In this case it's possible to save and load maps from db without problem I think

here is the link
http://www.megaupload.com/?d=04NYSYYO

to start the project just create a database alias in the ODBC administrator with microsoft dbase driver 5.0 named "World_countries_shp" and linked to the directory of the project

[sicorspa]

ok, I have found the source code of the resource loader

Code: Select all

implementation

{$R *.dfm}
{$R WorldMap.res}

uses Teeshp;

var memorystream_shp,memorystream_shx,memorystream_dbf : TMemoryStream;
    CampoValoreMondo, CampoValoreStato, TitoloMondo, TitoloDettaglio : String;

procedure TFormStatMeseNazioneWorldMap.CaricaMappa;
  var i : integer;
begin
  LoadResourceFile('mappashp2.shp', memorystream_shp);
  LoadResourceFile('mappashx2.shx', memorystream_shx);
  LoadResourceFile('mappadbf2.dbf', memorystream_dbf);

  if CDS.Active then
    CDS.Close;

  CDS.LoadFromStream(memorystream_dbf);

  CDS.Open;
  CDS.First;
  LoadMap(Series1,TResourceStream(memorystream_shp),TResourceStream(memorystream_shx),CDS,'ISO_2','POP_1994');

// change the ISO code of yugoslavia to serbia-montenegro in the first chart
  While Series1.Labels.IndexOfLabel('YU') >= 0 do
    Series1.Labels.Labels[Series1.Labels.IndexOfLabel('YU')] := 'CS';

  CDS.First;
  memorystream_shp.Seek(0,0);
  memorystream_shx.Seek(0,0);
  LoadMap(TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]),TResourceStream(memorystream_shp),TResourceStream(memorystream_shx),cds,'ISO_2','POP_1994');


  // change the ISO code of yugoslavia to serbia-montenegro in the second chart
  While TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).Labels.IndexOfLabel('YU') >= 0 do
    TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).Labels.Labels[TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).Labels.IndexOfLabel('YU')] := 'CS';

  CDSMondoInt.Open;

  CDSMondoInt.First;

  //** Disabile French colony

  Series1.XLabel[267] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[267] := '00';
  Series1.XLabel[266] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[266] := '00';
  Series1.XLabel[265] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[265] := '00';
  Series1.XLabel[264] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[264] := '00';
  Series1.XLabel[262] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[262] := '00';
  Series1.XLabel[261] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[261] := '00';

  //** Disabile the end of Russia

  Series1.XLabel[670] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[670] := '00';
  Series1.XLabel[679] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[679] := '00';
  Series1.XLabel[704] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[704] := '00';
  Series1.XLabel[705] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[705] := '00';

  //** Disabile France Islands

  Series1.XLabel[740] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[740] := '00';
  Series1.XLabel[741] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[741] := '00';
  Series1.XLabel[742] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[742] := '00';
  Series1.XLabel[743] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[743] := '00';
  Series1.XLabel[745] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[745] := '00';
  Series1.XLabel[746] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[746] := '00';
  Series1.XLabel[747] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[747] := '00';
  Series1.XLabel[748] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[748] := '00';
  
   //** Disabilito la macchia del sudafrica

  Series1.XLabel[735] := '00';
  TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).XLabel[735] := '00';

  i := 0;

  While (i < Series1.Count) do
  Begin
      if (Series1.XLabel[i] = 'FX') or
         (Series1.XLabel[i] = 'TF') or
         (Series1.XLabel[i] = 'GF') then //**
      Begin
        Series1.Delete(i);
        TWorldSeries(CTSubChart.Charts.Items[0].Chart.SeriesList[0]).Delete(i);
        i := i - 1;
      End;

    i := i + 1;
  End;
end;

procedure TFormStatMeseNazioneWorldMap.ColoraSerie(Series: TWorldSeries);
begin
  Series1.StartColor := clNavy;
  Series1.MidColor := clBlue;
  Series1.EndColor := clWhite;
  Series1.UsePalette := false;
  Series1.UseColorRange := true;
  Series1.MandatoryValueList.Modified := True;
  Series1.RefreshSeries;
end;


procedure TFormStatMeseNazioneWorldMap.CaricaDati(Series: TWorldSeries; Dataset: TDataSet; Campo : String);
  VAR I : Integer;
begin
  if (Dataset.Active) then
    Dataset.Open;

  DataSet.First;

  For i := 0 to Series.Count - 1 do
  Begin
    if Dataset.Locate('COD_ISO', VarArrayOf([Series.Labels.Labels[i]])[0],[]) then
      Series.ZValue[i] := Dataset.FieldByName(campo).AsFloat
    else
      Series.ZValue[i] := 0;
    Dataset.Next;
  End;

  ColoraSerie(Series);

  i := 0;

  While (i < Series.Count) do
  Begin
    if Series.ZValue[i] = 0 then
    Begin
      Series.Shapes[i].Transparent := True;
      Series.Transparency := 30;
    End
    else
    Begin
      Series.Shapes[i].Transparent := False;
      Series.Transparency := 0;
    End;
    i := i + 1;
  End;
end;

procedure TFormStatMeseNazioneWorldMap.FormCreate(Sender: TObject);
begin
  memorystream_shp := TMemoryStream.Create;
  memorystream_shx := TMemoryStream.Create;
  memorystream_dbf := TMemoryStream.Create;
end;

procedure TFormStatMeseNazioneWorldMap.FormDestroy(Sender: TObject);
begin
  memorystream_shp.Destroy;
  memorystream_shx.Destroy;
  memorystream_dbf.Destroy;
end;

procedure TFormStatMeseNazioneWorldMap.LoadResourceFile(aFile:string; ms:TMemoryStream);
var
   HResInfo: HRSRC;
   HGlobal: THandle;
   Buffer, GoodType : pchar;
   Ext:string;
begin
  ext:=uppercase(extractfileext(aFile));
  ext:=copy(ext,2,length(ext));
  Goodtype:=pchar(ext);
  aFile:=changefileext(afile,'');
  HResInfo := FindResource(HInstance, pchar(aFile), GoodType);
  HGlobal := LoadResource(HInstance, HResInfo);
  if HGlobal = 0 then
     raise EResNotFound.Create('Can''t load resource: '+aFile);
  Buffer := LockResource(HGlobal);
  ms.clear;
  ms.WriteBuffer(Buffer[0], SizeOfResource(HInstance, HResInfo));
  ms.Seek(0,0);
  UnlockResource(HGlobal);
  FreeResource(HGlobal);
end;

//this is to call the program from outside, so it can be more dynamic
procedure TFormStatMeseNazioneWorldMap.ApriMappa(CDSMondo: TClientDataSet; ValoreMondo, TitoloMond: String;
  CDSStato: TClientDataSet; ValoreStato, TitoloDett: String);
begin
  Series1.Clear;

  CDSMondoInt.CloneCursor(CDSMondo, false, false);

  CampoValoreMondo := ValoreMondo;

  TitoloMondo := TitoloMond;

  if TitoloMondo = '' then
    Chart1.Title.Visible := False
  else
  Begin
    Chart1.Title.Visible := True;
    Chart1.Title.Caption := TitoloMondo;
  End;

  CaricaMappa;

  CaricaDati(Series1, CDSMondoInt, ValoreMondo);

  CDSStatoInt.CloneCursor(CDSStato, false, false);

  CampoValoreStato := ValoreStato;

  TitoloDettaglio := TitoloDett;

  DBCDettaglioStato.Visible := False;

  ShowModal;
end;

think that this is all (as you can see these are the same procedure as the first program linked, only revisited for resource loader)

hope to haven't forget anything, however for any doubt or request, if I can, I will be glad to help you