I'm trying to use php and postgresql to generate a kml file (google earth) that have information of multiples places.
So I have multiples queries to retrieve all the information that I need.
Example:
SELECT c.latitude, c.longitude
FROM Circuit AS b, Instance AS c, InstanceNumber AS d
WHERE c.id = d.Instance
AND d.Circuit = b.id
AND b.code = 'Z3D2'
SELECT c.number, b.model, e.pollution
FROM Asset AS a, Model AS b, TowerNumber AS c, Line AS d, Instance AS e
WHERE COALESCE(a.Model,0)=b.id
AND c.Asset=a.id
AND a.Line=d.id
AND e.Asset=a.id
AND e.Source=1
AND d.name='Z3D2'
The field "number" in both TowerNumber and InstanceNumber should be similar.
I can't get all the information with just one query because some field are null for Instance.source and others are not.
Besides that the foreign Keys that connect tables are not filled in all tables so I have to do different "ways" to get the information.
1st: Line->Asset->TowerNumber->Instance
2nd: Line->Circuit->InstanceNumber->Instance
For Source='SD'which as 'latitude' and 'longitude' I have two use the 1st path.
For the other information that have Source="HD" I've to use the 2nd path.
I have these two queries as an example.
What I want to do is get the information in this two queries and create and array like:
number | model | pollution | latitude | longitude
0 | ZD1 | 7 | 48,48 | 2,22
1 | H3 | 6 | 48,49 | 2,24
... | ... | ... | ... | ...
And then I'll just write that information to the kml file where each place should have all the information and should look like:
<Placemark>
<name>$table[number]</name>
<ExtendedData>
<Data name=name_field_1>
<value>$table[model]</value>
</Data>
<Data name=name_field_2>
<value>$table[pollution]</value>
</Data>
...
...
<Point><coordinates>$table[longitude],$table[latitude]</coordinates></Point>
</Placemark>